Re: storing TZ along timestamps

On Mon, Jun 6, 2011 at 7:50 AM, Jim Nasby <jim(at)nasby(dot)net> wrote:
> On Jun 4, 2011, at 3:56 AM, Greg Stark wrote:
>> On Thu, Jun 2, 2011 at 8:58 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:
>>>
>>> I'm torn between whether the type should store the original time or the original time converted to GMT.
>>
>> This is the wrong way to think about it. We *never* store time
>> "converted to GMT".  When we want to represent a point in time we
>> represent it as seconds since the epoch.
> Right. Sorry, my bad.
>
>> The question here is how to represent more complex concepts than
>> simply points in time. I think the two concepts under discussion are
>> a) a composite type representing a point in time and a timezone it
>> should be interpreted in for operations and display and b) the
>> original input provided which is a text string with the constraint
>> that it's a valid input which can be interpreted as a point in time.
>
> My fear with A is that something could change that would make it impossible to actually get back to the time that was originally entered. For example, a new version of the timezone database could change something. Though, that problem also exists for timestamptz today, so presumably if it was much of an issue we'd have gotten complaints by now.

The common problem is daylight savings time being declared or
cancelled. This happens numerous times throughout the year, often with
short notice.

If you want to store '6pm July 3rd 2014 Pacific/Fiji', and want that
to keep meaning 6pm Fiji time no matter what decisions the Fijian
government makes over the next two years, you need to store the
wallclock (local) time and the timezone. The wallclock time remains
fixed, but the conversion to UTC may float.

If you are storing an point in time that remains stable no matter
future political decisions, you store UTC time and an offset. The
conversion to wallclock time may float, and your 6pm Fiji time meeting
might change to 5pm or 7pm depending on the policical edicts.

If you are only storing past events, its not normally an issue but
timezone information does occasionally get changed retroactively if
errors are discovered.

--
Stuart Bishop <stuart(at)stuartbishop(dot)net>
http://www.stuartbishop.net/