Re: storing TZ along timestamps

On Jun 2, 2011, at 7:48 PM, Jeff Davis wrote:
> On Thu, 2011-06-02 at 20:28 -0400, Robert Haas wrote:
>> But that doesn't seem like enough, because if someone adds '1 day',
>> knowing the offset isn't sufficient to figure out the answer. You
>> have to know where the DST boundary is.
>
> Good point, I guess the timezone itself needs to be stored. That's a
> little unfortunate, because timezones are somewhat of a moving target
> (which I think was Tom's point).
>
> That means that we'd need an entire history (and future?) of timezone
> definitions, and apply the timezone definition as of the associated
> timestamp to get the offset. Or, should we apply the timezone definition
> as of the "real" time the value was entered?

As someone else mentioned, timestamptz suffers the exact same problems.

I'm torn between whether the type should store the original time or the original time converted to GMT. I believe you would have the most accuracy if you stored the original time... but then indexing becomes problematic. I don't know if this data quality issue can be solved by anything short of somehow storing the actual timezone rule that was in place when the data was set.

Speaking of input; someone asked what timezone should be used as the "original" timezone. We should use whatever timezone was passed in with the value, and if one wasn't passed in we should use whatever the timezone GUC is set to (I'm assuming that's what timestamptz does).
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net