Re: Timestamp to time_t - Mailing list pgsql-hackers

>>>>> "Kevin" == Kevin Grittner <Kevin.Grittner@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
subsection6.30), I>> think we can safely ignore virtually everything it says about>> date/time handling.Kevin> Codd
wenton at some length about why this is the right thingKevin> to do.  He was highly critical of systems where adding a
monthKevin>to a date and then subtracting month from the result couldKevin> result in a date which was off from the
originaldate by asKevin> much as three days.  As a mathematician he felt strongly thatKevin> "(x + y) - y" should equal
x-- even when x is a date and y isKevin> 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 TIMESTAMPKevin> WITH TIME ZONE data type
wouldgo a long way toward makingKevin> 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.