Prev: WIP: generalized index constraints
Next: PGCluster-II Progress
From: Tom Lane on 15 Sep 2009 13:17
"David E. Wheeler" <david(a)kineticode.com> writes:
> On Sep 15, 2009, at 8:50 AM, Tom Lane wrote:
>> See TIMEZONE_HOUR, TIMEZONE_MINUTE field specifications, in particular

> try=# select extract(timezone_hour from '2001-02-16 20:38:40 America/
> Los_Angeles'::timestamptz);

You appear to be confusing what PG currently does with what the spec
says.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: "David E. Wheeler" on 15 Sep 2009 13:30
On Sep 15, 2009, at 10:17 AM, Tom Lane wrote:

>> try=# select extract(timezone_hour from '2001-02-16 20:38:40 America/
>> Los_Angeles'::timestamptz);
>
> You appear to be confusing what PG currently does with what the spec
> says.

Sorry, I thought you were referring to what PostgreSQL does. Would I
be wrong in thinking that the current behavior might be surprising to
some? I mean, I'd really like a timestamptz that tracked the tz or
offset that was used to create its value…

I'm sure that's been on the ToDo list for time immemorial.

Best,

David
--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Tom Lane on 15 Sep 2009 14:00
"Kevin Grittner" <Kevin.Grittner(a)wicourts.gov> writes:
> Andrew Gierth <andrew(a)tao11.riddles.org.uk> wrote:
>> Given that the spec requires that 2009-01-31 + interval 1 month =
>> 2009-02-31 (yes, really! see general rule 4 in subsection 6.30), I
>> think we can safely ignore virtually everything it says about
>> date/time handling.

> Codd went on at some length about why this is the right thing to do.
> He was highly critical of systems where adding a month to a date and
> then subtracting month from the result could result in a date which
> was off from the original date by as much as three days. As a
> mathematician he felt strongly that "(x + y) - y" should equal x --
> even when x is a date and y is an interval.

[ shrug... ] We *have* that property, for sane cases such as adding and
subtracting a fixed number of days. For less sane cases, I would point
out to Codd that the current calendar system was not designed by
mathematicians, and trying to superimpose strict mathematical rules on
it just leads to nonsense (like the spec's requirements).

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Andrew Gierth on 15 Sep 2009 14:01
>>>>> "Kevin" == Kevin Grittner <Kevin.Grittner(a)wicourts.gov> writes:

>> Given that the spec requires that 2009-01-31 + interval 1 month =
>> 2009-02-31 (yes, really! see general rule 4 in subsection 6.30), I
>> think we can safely ignore virtually everything it says about
>> date/time handling.

Kevin> Codd went on at some length about why this is the right thing
Kevin> to do. He was highly critical of systems where adding a month
Kevin> to a date and then subtracting month from the result could
Kevin> result in a date which was off from the original date by as
Kevin> much as three days. As a mathematician he felt strongly that
Kevin> "(x + y) - y" should equal x -- even when x is a date and y is
Kevin> an interval.

Mathematical elegance is all very well, but until you convince the real
world to abandon inelegant concepts like months with unequal lengths,
the database has to behave in ways that are useful within the constraints
of actual practice.

(To me, the fact that the spec's idea of 2009-01-31 + 1 month
corresponds to a value that current_date will never be equal to is a
far greater show-stopper.)

To look specifically at timezones, the problem with the spec here is that
it doesn't store _timezones_, it stores _timezone offsets_. So per the spec,
(timestamp with time zone '2009-01-01 12:00:00 +0000' + interval 6 months)
would be equal to '2009-07-01 12:00:00 +0000' (REGARDLESS of what the
server's timezone is configured as), which is remarkably non-useful; also,
the spec's idea of + interval 24 hours is equal to + interval 1 day, whereas
here in the real world those aren't the same thing at all.

Worse still, the spec defines the behaviour of DST as follows: the server
has a specific timezone _offset_, that offset _changes_ on DST start/end,
and conversions between timestamp w/o tz and timestamptz are done USING THE
SERVER'S CURRENT OFFSET, NOT THE OFFSET AS IT WOULD HAVE BEEN AT THE TIME
VALUE BEING CONVERTED. This is so wrong there aren't even words to describe
how wrong it is.

Kevin> Personally, I think that including time zone in the TIMESTAMP
Kevin> WITH TIME ZONE data type would go a long way toward making
Kevin> some useful features work.

It would break far too many other things in the process.

If you want to store both a timestamp and an associated timezone you can do
it right now, using a composite type or two columns, with the advantage that
you get semantics that you can rely on.

--
Andrew.

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Andrew Dunstan on 15 Sep 2009 14:19


Tom Lane wrote:
> For less sane cases, I would point
> out to Codd that the current calendar system was not designed by
> mathematicians, and trying to superimpose strict mathematical rules on
> it just leads to nonsense (like the spec's requirements).
>
>
>

He's not listening ...

Strangely (or perhaps not), he stopped listening around the time I
started working on Postgres ...

cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: WIP: generalized index constraints
Next: PGCluster-II Progress