Re: timestamp with time zone a la sql99

Dennis Bjorklund <db(at)zigo(dot)dhs(dot)org> writes:
> And exactly what issues is it that you see? The only thing I can think of
> is if you have a timestamp and then add an interval to it so we jump past
> the daylight saving time change date. Then the new timestamp will keep the
> old timezone data of say +01 even though we now have jumped into the
> daylight saving period of +02.

Isn't that sufficient? You can't design a datatype by thinking only of
the data values it stores; you have to think about the operations you
intend to provide as well. A non-DST-capable timestamp datatype is
inherently a few bricks shy of a load. (BTW we really need to fix
the interval type as well...)

At bottom, what I want to be able to do is say
'2004-10-22 10:50:16.916003 America/New_York'
and have the datatype preserve *all* of the information in that. You
are complaining because the existing type only remembers the equivalent
universal time and not the timezone spec. Why should I be satisfied if
it stores only the GMT offset and not the knowledge of which timezone
this really is?

> My current thinking is that storing the time zone value as HH:MM is
> just fine and you avoid all the problems with political changes of when
> the DST is in effect or not.

This is fundamentally misguided. Time zones *are* political whether you
like it or not, and people *do* expect DST-awareness whether you like it
or not. If you still use any computer systems that need to be reset
twice a year because their designers thought DST was not their problem,
don't you roundly curse them every time you have to do it?

If you were planning to store a real (potentially DST-aware) timezone
spec in the data values, I'd be happy. But storing a fixed GMT offset
is going to be a step backwards compared to existing functionality. The
fact that it's sufficient to satisfy the DST-ignorant SQL spec does not
make it a reasonable design for the real world.

One way to do this would be to create a system catalog with entries for
all known timezones, and then represent timestamptz values as universal
time plus an OID from that catalog. There are other ways that small
integer codes could be mapped to timezones of course.

regards, tom lane