Re: storing TZ along timestamps

On Jul 22, 2011, at 10:33 AM, Robert Haas wrote:
> On Thu, Jul 21, 2011 at 7:05 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:
>> On Jul 21, 2011, at 5:30 PM, Christopher Browne wrote:
>>> - I'd commend capturing NOW() in a timestamptz field. That gives you:
>>> 1. What time the DB server thought it was, in terms of UT1
>>> 2. What timezone it thought was tied to that connection.
>>
>> Except that it doesn't, and that's exactly the problem I'm trying to solve here. I want to know what timezone we were using when we put a value into timestamptz, which then got converted to UT1. Without a reliable way to store what the timezone *was* at that time, we have no way to go back to it.
>>
>> Now, we can debate whether it makes more sense to store the original time without conversion to UT1, or whether we should store the time after converting it to UT1 (or whether we should offer both options), but that debate is pointless without a good way to remember what timezone it started out in.
>>
>> Arguably, we could just create an add-on data type for storing that timezone information, but that seems pretty daft to me: you're stuck either storing raw text which takes what should be a 12 byte datatype up to a 20-30 byte type (8 byte timestamp + varlena + text of timezone name), or you end up with major problems trying to keep an enum in sync with what the database has available in it's ZIC database.
>
> You have those same problems trying to include the time zone
> information in some new timestampreallyhasthetz data type, though.
>
> This problem reminds me a great deal of the problems associated with
> managing security labels for SE-Linux. There aren't that many
> distinct values, so ideally it would be nice to store an OID -> string
> mapping somewhere and just store the OIDs in the main table. But a
> new security label can appear at any time, and it doesn't work to have
> the transaction that discovers it do the insert into the mapping
> table. Time zones have the same problem, more or less. Now, maybe if
> we had non-transactional tables like Alvaro keeps muttering about...

Hrm, don't we only pull in ZIC info on a reload? Or do we actually refer to it dynamically all the time? Perhaps we can enforce that we'll only recognize new TZ info as part of a config reload?

Josh Berkus also made a good point that this does introduce the risk that you could end up moving data to a different server, that has a different ZIC database (perhaps via replication); at which point the fit could hit the shan (or the excrement could impact the cooling device...). So perhaps the only reasonable way to handle this is to actually load ZIC data into the database itself.
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net