Re: timestamp with time zone a la sql99

This thread has been added as a link on the TODO list under TODO.detail.

---------------------------------------------------------------------------

Dennis Bjorklund 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. 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;
> a
> ------------------------
> 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;
> timezone
> ------------------------
> 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
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073