Skip site navigation (1) Skip section navigation (2)

storing TZ along timestamps

From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: storing TZ along timestamps
Date: 2011-05-27 20:43:28
Message-ID: 1306528155-sup-8852@alvh.no-ip.org (view raw or flat)
Thread:
Lists: pgsql-hackers
Hi,

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.

Right now we rely on the tzdata files on disk for things like
pg_timezone_names and other accesses of TZ data; so the files are the
authoritative source of TZ info.  So we need to ensure that whenever the
files are updated, the catalogs are updated as well.  
I think we could make this work if we "refreshed" the catalog from the
files on SIGHUP if the directory changes (say, a new timezone is
created).  Note that I am currently proposing to store only the zone
names in the catalog, not the full TZ data.

Are there objections to the general idea?  If not, I'll flesh a more
complete proposal.

-- 
Álvaro Herrera <alvherre(at)commandprompt(dot)com>

Responses

pgsql-hackers by date

Next:From: Robert HaasDate: 2011-05-27 20:55:07
Subject: Re: Reducing overhead of frequent table locks
Previous:From: Robert HaasDate: 2011-05-27 19:02:53
Subject: Re: tackling full page writes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group