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-0114:00 CET'); dennis=# INSERT INTO foo VALUES ('2003-08-21 15:00 PST');
dennis=# SELECT a FROM foo; a ------------------------ 1993-02-04 13:00:00+00 1999-06-01 14:00:00+01
2003-08-2115:00:00-08 dennis=# SELECT a AT TIME ZONE 'CET' FROM foo; timezone ------------------------
1993-02-0414: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
possible?
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