Re: Timezone database changes - Mailing list pgsql-hackers

On 10/11/07, Gregory Stark <stark@enterprisedb.com> wrote:
> "Tom Lane" <tgl@sss.pgh.pa.us> writes:
>
> > "Trevor Talbot" <quension@gmail.com> writes:
> >> On 10/11/07, Magne Mæhre <Magne.Mahre@sun.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?

October 29, 2006, 1:15am: PDT or PST?

Even if you ignore overlap points like that, DST status is a piece of
semantic information the human retrieving the data may want to know.
It doesn't make much sense for an app to avoid the database's
perfectly good knowledge of the local timezone to get it.

> > Also note the possibility of coercing one type to the other on-the-fly
> > for display, or using the AT TIME ZONE construct.

Sure, but that's simply a workaround like tagging different zones
yourself is.  This single case isn't terribly important, it's just a
non-future-appointment one where remembering the local zone makes
sense.

If we change it a bit so that it regularly transports data to a
central office, you still want to know what time zone it belongs to.
Right now, the local office's zone rules matter because you need it to
convert to UTC properly.  Instead, it should be the central office's
zone rules that matter for temporary conversion and reporting, because
you really don't want the original data changed at all.  The original
data is the legitimate record, not the conversion to UTC.

This can all be done manually by applications today, of course.  It
would just be nice to take advantage of PostgreSQL's time zone
knowledge more easily in these situations.


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

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

While I agree that UTC storage is definitely a needed option, I was
trying to point out in the scenario above that sometimes an event
recorded at a specific moment in time *is* local time.  Birth
certificates aren't in UTC.  Usually there's no practical difference,
but there can be a semantic difference.

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

The link I posted upthread was someone who ran into something
unexpected.  There wasn't enough detail to figure out what, exactly,
just that something related to zones changed and surprised them.

And no, I don't think it's urgent either; the current behavior is
known and fairly easy to understand.  It's just that some applications
need a different set of semantics.