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

On 08/29/2014 04:59 AM, Kevin Grittner wrote:
> 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.

FWIW, MS SQL's DateTimeOffset data type:

http://msdn.microsoft.com/en-AU/library/bb630289.aspx

is much more like what I, when I was getting started, expected TIMESTAMP
WITH TIME ZONE to be. We don't really have anything equivalent in
PostgreSQL.


The PostgreSQL implementation merits some highlighted clear explanation
in the documentation, explaining the concept of a point in absolute time
(the first person to mention relativity gets smacked ... oh, darn) vs a
wall-clock value in local time. It should also discuss the approach of
storing a (instant timestamptz, timezone text) or (instant timestampts,
tzoffset smallint) tuple for when unambiguous representation is required.

(I guess I just volunteered myself to write a draft of that).


BTW, it might be interesting to have a validated 'timezone' data type
that can store time zone names or offsets, for use in conjunction with
timestamptz to store a (timestamptz, timezone) tuple. Though also
complicated - whether 'EST' is Australian or USA Eastern time is
GUC-dependent, and it can't just be expanded into Australia/Sydney at
input time because "EST" is always +1000 while Australia/Sydney could
also be EDT +1100 . I hate time zones. It'd probably have to expand
abbrevs to their UTC offsets at input time.



-- Craig Ringer                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services