Re: Why data of timestamptz does not store value of timezone passed to it?

"ktm(at)rice(dot)edu" <ktm(at)rice(dot)edu> wrote:

> On Thu, Aug 28, 2014 at 03:33:56PM -0400, Bruce Momjian wrote:

>> So the standard requires storing of original timezone in the
>> data type?  I was not aware of that.
>
> I do not have a copy of the SQL 92 spec, but several references
> to the spec mention that it defined the "time zone" as a format
> "SHH:MM" where S represents the sign (+ or -), which seems to be
> what PostgreSQL uses.

I just took a quick look at the spec to refresh my memory, and it
seems to require that the WITH TIME ZONE types store UTC (I suppose
for fast comparisons), it requires the time zone in the form of a
hour:minute offset to be stored with it, so you can determine the
local time from which it was derived.  I concede that this is not
usually useful, and am glad we have a type that behaves as
timestamptz does; but occasionally a type that behaves in
conformance with the spec would be useful, and it would certainly
be less confusing for people who are used to the standard behavior.

Basically, both store a moment in time in UTC, and display it with
offset in hours and minutes; but the standard says it should show
you that moment from the perspective of whoever saved it unless you
ask for it in a different time zone, while PostgreSQL always shows
it to you from the perspective of your client connection's time
zone.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company