Re: storing TZ along timestamps

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
usingwhen 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
shouldstore the time after converting it to UT1 (or whether we should offer both options), but that debate is pointless
withouta 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
thedatabase has available in it's ZIC database. 
--
Jim C. Nasby, Database Architect                   jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net