On Jul 19, 2011, at 11:22 AM, Ian Caulfield wrote:
> On 19 July 2011 17:11, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
>> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
>>>> Josh Berkus <josh@agliodbs.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
thehistory 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
referto it a common timezone (such as UTC), OR you can refer to it at it's original, local time. That's our exact need
forthis: we have different businesses that operate in different timezones. Generally, we only care about things in
localtime, but there are cases (such as event logging) where we could care about local *OR* unified time.
--
Jim C. Nasby, Database Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net