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

On Thu, 21 Oct 2004, Tom Lane wrote:

> > I've made a partial implementation of a datatype "timestamp with time
> > zone" as described in the sql standard. The current type "timestamptz"  
> > does not store the time zone as a standard one should do.
> 
> I'm aware that there are aspects of the spec behavior that appear to
> require that, but is it really an improvement over the implementation
> we have?

Improvement and improvement. The actual time value is of course the same
(the utc part of a timestamp) and the only thing extra you get is that the
time zone is stored. The extra information you do have now, when stored in
this way, is that you store both a utc time and a local time. Will any
application ever need that? Who knows? I think it makes sense and is an
easier model to think about then what pg uses today. So I would use it
even if it means using 2 bytes more storage then what timestamptz do

Just that it is standard also makes it useful. The more things of the
standard we support the easier it is to move between databases. This is
important to me.

I also want to make a general statement that I think that whenever we use
standard syntax we should give it a standard semantics. I don't mind
extensions at all, but as much as we can we should make sure that they
don't clash with standard syntax and semantics.

>  This is an area in which the standard is pretty brain-dead
> --- the entire concept of a "time with time zone" datatype is rather
> suspect, for instance.

I havn't look that much at "time with time zone" yet, just timestamps.
I can't see why time with time zone should not also be supported. I can't
really imagine it being used without a date, but if someone wants to store
timestamps as a date+time with time zone, then why not. It would be extra
work tu is it instead of a timestamp (especially for cases where the time
wraps over to the prev/next day), but hey.

> In particular, I wonder how you will handle daylight-savings issues.
> The spec definition seems to preclude doing anything intelligent with
> DST, as they equate a timezone with a fixed offset from UTC.  That's
> not how it works in (large parts of) the real world.

The tz in the standard is a offset from utc, yes. So when you store a
value you tell it what offset you use. If you are using daylight-savings
time it might be +02 and if not dst it might be +01. What else would you
want to do with it? It's not like you can do anything else with it in pg
as of today, can you?

The stored tz does not say what region of the globe you are in, it says
the distance away from utc in minutes that you are. I could imagine
another datatype that stores the time zone as name, but that's not what
timestamp with time zone does.

-- 
/Dennis Björklund