Re: storing TZ along timestamps

On Jul 19, 2011, at 11:22 AM, Ian Caulfield wrote:
> On 19 July 2011 17:11, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
>>>> Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>>>>> The timestamp and the timezone in which that timestamp was
>>>>> entered are two separate pieces of data and *ought* to be in two
>>>>> separate fields.
>>>
>>>> So, if you're grabbing a timestamp and the time zone for it, how
>>>> do you ensure you've done that atomically if you're at the
>>>> boundary of a DST change?
>>>
>>> In my view of the world, the timezone that you are in is not an
>>> object that changes across a DST boundary.
>>
>> You're right -- the moment in time should be fixed like in the
>> current PostgreSQL "timestamp with time zone", and the time zone
>> doesn't change with DST. Not an intentional read herring, but
>> definitely some muddy thinking there.
>
> There was an earlier point made that if someone puts eg 5pm local time
> two years in the future into the database, and then the DST boundary
> gets moved subsequently, some applications would like the value to
> still say 5pm local time, even though that means it now refers to a
> different point in absolute time - this potentially seems like a
> useful feature. Retroactive timezone changes wouldn't make a lot of
> sense in this case though...

Right; and timezone's aren't supposed to change retroactively. The ZIC database is specifically setup so that it knows the history of TZ changes and deals with the past correctly.

> I guess there are three concepts of time here - an absolute fixed time
> with no reference to a timezone, a time with a timezone that is still
> set as a fixed point in time, or a local time in a specific timezone
> that would move if the timezone definition changed.

Or, another way to put the third class: a timestamp that remembers what it's original timezone was so that you can refer to it a common timezone (such as UTC), OR you can refer to it at it's original, local time. That's our exact need for this: we have different businesses that operate in different timezones. Generally, we only care about things in local time, but there are cases (such as event logging) where we could care about local *OR* unified time.
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net