Alvaro Herrera <alvherre@2ndquadrant.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
