Re: timestamp with time zone a la sql99

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Re: timestamp with time zone a la sql99

Bruce Momjian-2

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 [hidden email])
>

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org