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

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?)

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services