Re: storing TZ along timestamps

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Fri, May 27, 2011 at 04:43:28PM -0400, Alvaro Herrera wrote:
> Hi,
>
> One of our customers is interested in being able to store original
> timezone along with a certain timestamp.

I've felt that pain here and there too...

> 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.

Problem with this approach (mapping external time zone names to OIDs)
is: dump/restore would only be meaningful if you "carry over" the time
zone data, right?

That is: two independent systems are likely to have different mappings
(even if at some point they have the "same" TZ data?)

What would be a solution to that?

(a) A central, "official" catalog, with only additions, never deletions
(perhaps with some space carved out for "local" additions, to minimize
conflicts)?
(b) A hash of the time zone name?

Both not very good ideas, I know. Although (a) might be less bad than it
seems. Most Unixoids (including OSX) seem to have basically Olson's.
Don't know about Windows, but it might seem feasible to make some
mapping (or union). Only important rule: no backtrack :-)

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFN4JWtBcgs9XrR2kYRAt+mAJ0atx3u6pll50+s4vVwCKZUjqmnSQCffWNe
gzSFgRCFUvsd8pbH1Qm/ho4=
=FVhO
-----END PGP SIGNATURE-----