Re: Timezone database changes

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> "Trevor Talbot" <quension(at)gmail(dot)com> writes:
>> On 10/11/07, Magne M=E6hre <Magne(dot)Mahre(at)sun(dot)com> wrote:
>>> Trevor Talbot wrote:
>>>> That situation might sound a bit contrived, but I think the real point
>>>> is that even for some records of observed times, the local time is the
>>>> authoritative one, not UTC.
>>>
>>> ...and for that scenario you have TIMESTAMP WITHOUT TIME ZONE
>
>> But that doesn't give you DST-sensitive display for free, which is
>> tempting for application use, especially if the application is meant
>> to be suitably generic.
>
> If you are dealing only in local time, what do you need timezone for at
> all?
>
> Also note the possibility of coercing one type to the other on-the-fly
> for display, or using the AT TIME ZONE construct.

I think there are clearly use cases for all three semantics:

1) Specified time of day in whatever the current time zone is
(i.e. our current TIMESTAMP WITHOUT TIME ZONE)

2) Specific moment in time
(i.e. stored in UTC which is unaffected by time zone rules)

3) Specified time of day in specified time zone
(equivalent to #2 except when the time zone rules change)

In the SQL spec #2 and #3 are interchangeable since the time zone rules there
can never change. But in the real world as we've seen they do.

Surely #2 is a must-have. There has to be a data type for representing a fixed
moment in time unaffected by any time zone rules. Anything recording events --
which of course occurred at a specific moment in time -- needs it and there
are a whole lot of databases which do just that. Actually in my experience
most tables have one or sometimes more timestamps of that nature.

The lack of #3 doesn't seem terribly pressing given how rarely the time zone
rules change. Even with the latest shenanigans I don't think anyone's run into
any unexpected problems.

I would say if someone implemented #3 then it would make sense to have it. It
would probably make sense for calendaring applications where the user is
manually entering a timezone and probably means that time in that timezone
even if the moment in time that it represents changes due to the rules
changing.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com