Postgres Pro
Downloads
Home   >   mailing lists

Thread: Timestamp with time zone issue

Timestamp with time zone issue

From
"Jim Ballard"
Date:
PostgreSQL's "timestamp with time zone" implementation seems to fall short of the standard in the following way.
 
The standard calls for this datatype to be stored as a timestamp and a separate time zone displacement.  This allows for display of such data as it was entered into the system and also allows for the system to recognize two times in different time zones as representing the same universal time.
 
But PostgreSQL converts an entered timestamp with time zone value into GMT and stores only that.  It always displays such a value in the local time zone, not as entered.
 
Note that PostgreSQL's time with time zone type is implemented differently.  Such times are always displayed as entered (both parts are stored), and equivalent universal times are recognized as such.
 
Is this something that might be changed in the future?  Also, will a timestamp without time zone be supported at some point?
 
Thanks
Jim Ballard
Netezza Corp.
 

Re: Timestamp with time zone issue

From
Tom Lane
Date:
"Jim Ballard" <jballard@netezza.com> writes:
> PostgreSQL's "timestamp with time zone" implementation seems to fall short =
> of the standard in the following way.

There is considerable prior discussion of this in the mail list
archives, which you should consult.  But the bottom line is: Postgres'
timestamp datatype is not, and is not intended to be, exactly compatible
with either of the spec's TIMESTAMP WITH TIME ZONE or TIMESTAMP WITHOUT
TIME ZONE.  The people who have worked on the code have zero interest
in breakin^H^H^H^H^H^Hchanging it to match either of those.  At some
point someone might add additional datatypes that meet the spec
behavior; but it doesn't seem like a high priority to me.  I have not
seen very many, if any, convincing examples of applications where the
spec datatypes are better than what we have.

There is an ongoing disagreement as to whether format_type should
display "timestamp" as "timestamp with time zone" or just "timestamp".
I happen to fall in the second camp, so I won't attempt to explain to
you why the first camp thinks that way is right ...

            regards, tom lane