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

Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> wrote:
> 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.
>
> I remember we tried to implement this some years ago (IIRC alongside
> Alexey Klyukin who might remember more details).  I couldn't find the
> thread, but one of the first problems we encountered was that we wanted
> to avoid storing the text name of the timezone on each datum; we had the
> idea of creating a catalog to attach an OID to each timezone, but that
> turned very quickly into a horrid mess and we discarded the idea.
>
> (For instance: if a new timezone is added in a new tzdata release, it
> needs to be added to the catalog, but how do you do that in minor
> releases?)

But the standard doesn't say anything about storing a time zone
*name* or *abbreviation* -- it requires that it be stored as UTC
with the *offset* (in hours and minutes).  That makes it pretty
close to what we have -- it's all about a difference in
presentation.  And as far as I can see it completely dodges the
kinds of problems you're talking about.

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