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

From Bruce Momjian
Subject Re: timestamp with time zone a la sql99
Date
Msg-id 200506050020.j550KQD28237@candle.pha.pa.us
Whole thread Raw
In response to timestamp with time zone a la sql99  (Dennis Bjorklund <db@zigo.dhs.org>)
List pgsql-hackers
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@postgresql.org)
> 

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


pgsql-hackers by date:

Previous
From: "Serguei A. Mokhov"
Date:
Subject: Re: pg_upgrade project: high-level design proposal of
Next
From: Bruce Momjian
Date:
Subject: Re: [PATCHES] WAL bypass for CTAS