Martijn,
> I agree. One issue I can think of is that if you store each timestamp
> as a (seconds,timezone) pair, the storage requirements will balloon,
> since timezone can be something like "Australia/Sydney" and this will
> be repeated for every value in the table. I don't know how to deal
> easily with this since there is no unique identifier to timezones and
> no implicit order.
>
> The only solution I can think of is have initdb create a pg_timezones
> table which assigns an OID to each timezone it finds. Then the type can
> use that.
>
> I think this is a good solution actually, any thoughts?
Using OID's is a good idea, but I think a canonical list of known
timezone to OID mappings must be maintained and shipped with the
PostgreSQL core.
If OID's are generated at initdb time, there's a great risk that the
OID's will differ between databases using different versions of
PostgreSQL. That in turn will have some negative implications for data
exchange.
Regards,
Thomas Hallgren