timestamp with time zone a la sql99

From: Dennis Bjorklund <db(at)zigo(dot)dhs(dot)org>
To: pgsql-hackers(at)postgresql(dot)org
Subject: timestamp with time zone a la sql99
Date: 2004-10-21 11:22:40
Message-ID: Pine.LNX.4.44.0410211300430.2015-100000@zigo.dhs.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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. So I've made a
new type I've called timestampstdtz that does store the time zone as the
standard demands.

Let me show a bit of what currently works in my implementation:

dennis=# CREATE TABLE foo (
a timestampstdtz,

primary key (a)
dennis=# INSERT INTO foo VALUES ('1993-02-04 13:00 UTC');
dennis=# INSERT INTO foo VALUES ('1999-06-01 14:00 CET');
dennis=# INSERT INTO foo VALUES ('2003-08-21 15:00 PST');

dennis=# SELECT a FROM foo;
1993-02-04 13:00:00+00
1999-06-01 14:00:00+01
2003-08-21 15:00:00-08

dennis=# SELECT a AT TIME ZONE 'CET' FROM foo;
1993-02-04 14:00:00+01
1999-06-01 14:00:00+01
2003-08-22 00:00:00+01

My plan is to make a GUC variable so that one can tell PG that constructs
like "timestamp with time zone" will map to timestampstdtz instead of
timestamptz (some old databases might need the old so unless we want to
break old code this is the easiest solution I can find).

I've made an implicit cast from timestampstdtz to timestamptz that just
forgets about the time zone. In the other direction I've made an
assignment cast that make a timestamp with time zone 0 (that's what a
timestamptz is anyway). Would it be possible to make it implicit in both
directions? I currently don't think that you want that, but is it

With the implicit cast in place I assume it would be safe to change
functions like now() to return a timestampstdtz? I've not tried yet but I
will. As far as I can tell the cast would make old code that use now() to
still work as before.

Any comments before I invest more time into this subject?

/Dennis Björklund


Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2004-10-21 14:29:07 Re: timestamp with time zone a la sql99
Previous Message ronzo 2004-10-21 10:44:33 Postresql 8.0 Beta 3 - SELECT ... FOR UPDATE