Hi, On May 27, 2011, at 11:43 PM, Alvaro Herrera wrote: > > One of our customers is interested in being able to store original > timezone along with a certain timestamp. > > It is currently possible to store a TZ in a separate column, but this is > a bit wasteful and not very convenient anyway. > > There are all sorts of UI issues that need to be resolved in order for > this to be a complete feature proposal, but the first thing that we > discussed was what is the storage going to look like. Of course, one > thing we don't want is to store the complete TZ name as text. > > So the first thing is cataloguing timezone names, and assigning an ID to > each (maybe an OID). If we do that, then we can store the OID of the > timezone name along the int64/float8 of the actual timestamp value.
So, I'd think there are 2 reasonable approaches to storing the timezone part: 1. Store the timezone abbreviation (i.e. 'EST' along w/ the timestamp data). 2. Assign OID to each of the timezones and store it w/ the timestamp. The first option seem to avoid the necessity of creating a new system catalog for timezone information and the burden of updating it, because current implementation is already capable of translating abbreviations to useful timezone information. The question is, whether just a TZ abbreviation is sufficient to uniquely identify the timezone and get the offset and DST rules. If it's not sufficient, how conflicting TZ short names are handled in the current code (i.e. 'AT TIME ZONE ...')? The second choice doesn't avoids the issue of ambiguous names, although it requires moving TZ information inside the database and providing some means to update it. There were mentions of potential problems w/ pg_upgrade and pg_dump, if we add a massive amount of oids for the timezones. What are these problems specifically? I'd thing that storing TZ abbreviations is more straightforward and easier to implement, unless there are too ambiguous to identify the timezone correctly. > Note that I am currently proposing to store only the zone > names in the catalog, not the full TZ data. Where would we store other bits of timezone information? Wouldn't it be inconvenient to update names in system catalogs and DST rules elsewhere? Alexey. -- Command Prompt, Inc. http://www.CommandPrompt.com PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
