Re: storing TZ along timestamps

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...

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.

Ian