Re: Timezone database changes - Mailing list pgsql-hackers

On 10/10/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Trevor Talbot" <quension@gmail.com> writes:
> > Actually, what I meant at least (not sure if others meant it), is
> > storing the value in the timezone it was entered, along with what zone
> > that was.  That makes the value stable with respect to the zone it
> > belongs to, instead of being stable with respect to UTC.  When DST
> > rules change, the value is in effect "reinterpreted" as if it were
> > input using the new rules.
>
> What happens if the rules change in a way that makes the value illegal
> or ambiguous (ie, it now falls into a DST gap)?

That's a good question.  I have a vague memory of something that
absolutely needed to accept such values (as this would have to)
choosing a reasonable way to interpret them.  In the case of jumps
forward, e.g. 1:59->3:00, a time of 2:15 is assumed to be on the
previous scale, and thus interpreted as 3:15.  For overlapping times,
it picks one but I don't recall which.

Unfortunately I don't remember where I picked that up.  It might have
been a semi-standard, or it might have been someone's personal theory.

Your later example of midnight EDT + 3 months wanting to be midnight
EST is a good one, so what I said earlier about internally converting
to UTC is not something you want to do eagerly.  I'd wondered why
upthread Kevin mentioned using separate date and time types instead of
just using timestamp; now I know.  This point should go in any
documentation enhancement too.

> But perhaps more to the point, please show use-cases demonstrating that
> this behavior is more useful than the pure-UTC behavior.  For storage of
> actual time observations, I think pure-UTC is unquestionably the more
> useful.  Peter's example of a future appointment time is a possible
> counterexample, but as observed upthread it's hardly clear which
> behavior is more desirable in such a case.

Actually, it usually is, because a human picked one ahead of time.
For example, if the appointment is set for 3pm in London, the London
zone is the authoritative one, so that's what you store it in the DB
as.  If you're viewing it in NZ time, and the NZ DST rules change, so
does what you see.  If the London rules change, what you see in NZ
still changes, but what you see in London does not.

Choosing UTC in that scenario only works if the London DST rules don't
change.  Choosing the referencing timezone (London) when you store the
value works if either one changes.

If an organization is regularly scheduling such things, they might
just settle on UTC anyway to avoid confusion, in which case you store
values in UTC and get the same behavior as you do currently.

I don't know what this person was doing, but I gather sticky timezones
was preferable to them:
http://archives.postgresql.org/pgsql-general/2007-08/msg00461.php

Thinking that it might have had out of date zone rules brings up an
interesting scenario though.  Consider a closed (no networking or
global interest) filing system in a local organization's office, where
it's used to record the minutes of meetings and such via human input.
It would seem that the correct time to record in that case is in fact
the local time, not UTC.  If that system is left alone for years, and
does not receive any zone rule updates, it will likely begin storing
the wrong UTC values.  When the data is later transported out
(upgrade, archive, whatever), it will be incorrect unless you use that
particular snapshot of the zone rules.

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.