Re: storing TZ along timestamps

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