Alvaro Herrera <alvhe...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers