Re: timestamp with time zone a la sql99 - Mailing list pgsql-hackers

On Fri, 22 Oct 2004, Tom Lane wrote:

> At bottom, what I want to be able to do is say
>     '2004-10-22 10:50:16.916003 America/New_York'

Yes, that's what we said in the last mail and I think there is a value in
having something like this.

> 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?

You don't need to be satisfied with it. I think a type like the above
would be fine to have. It should however not be called "TIMESTAMP WITH
TIME ZONE" because there is already a definition of that type. We can not
hijack standard types. I would not mind a type like TIMESTAMP WITH TIME
ZONE NAME (or some other name). I could even imagine that I could
implement something like that one day.

> > 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.

And I never said that time zones are not political, just that HH:MM is a
usable approximation that works fairly well.

> But storing a fixed GMT offset is going to be a step backwards compared
> to existing functionality.

It's not a step backwards since you can do everything you can do with the 
current type plus a little bit more. It's however not a step to the 
datatype discussed above.

> 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.

This is just fine. You try to make it sound like I am against such a
datatype, I am not. It's however not the datatype that we can expect
applications and other databases to use. So why should we settle for only
that type. Just because you can make a perfect datatype it doesn't mean
that the standard datatype should just be ignored.

What would you store when the user supplies a timestamp like '2004-10-22
17:21:00 +0200'. Should you reject that because you don't know the 
time zone name? So your datatype will not work for applications that try 
to be compatable with many databases by using the standard?

Maybe one could make a datatype called TIMESTAMP WITH TIME ZONE that can
accept both HH:MM and TimeZoneName. Whenever you store values with HH:MM 
time zones you will get the same problem when you add an interval as the 
standard type has.

-- 
/Dennis Björklund