Re: Timezone database changes

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Aidan Van Dyk <aidan(at)highrise(dot)ca>
Cc: Peter Eisentraut <peter_e(at)gmx(dot)net>, Trevor Talbot <quension(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org, Magne Mæhre <Magne(dot)Mahre(at)sun(dot)com>
Subject: Re: Timezone database changes
Date: 2007-10-10 14:30:44
Message-ID: 16109.1192026644@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Aidan Van Dyk <aidan(at)highrise(dot)ca> writes:
> * Peter Eisentraut <peter_e(at)gmx(dot)net> [071010 09:58]:
>> If we make an appointment at 12-November-2007 at 10:00 CET (winter time) and
>> next week those in charge decide to postpone the change to winter time from
>> 28-October-2007 to 25-November-2007, what becomes of the appointment? Do we
>> still meet when the hands point to "10", or when?

> And to make matters worse, what if your appointment includes a audio(or
> video) conference with colleagues sitting in London, who you've told to
> meet at 16:00 (OK, my timezones/daylight savings, etc may be off)

Exactly ... there is more than one right answer here. The answer that
PG's TIMESTAMP WITH TIME ZONE code deems to be right is that UTC is
reality. That's a definition that is indeed useful for a wide variety
of real-world problems. In a lot of cases where it's not so useful,
TIMESTAMP WITHOUT TIME ZONE does the right thing. I'm not sure that
there's a significant use-case for a third behavior, and I definitely
don't think you can make an argument from first principles that the
UTC-based definition is wrong.

(FWIW, Red Hat has been struggling with this exact problem of
cross-time-zone meeting times for some years now, and has pretty much
arrived at the conclusion that company meeting times are to be defined
in UTC...)

The arguments that have been made for storing a zone along with the UTC
value seem to mostly boil down to "it should present the value the same
way I entered it", but if you accept that argument then why do we have
DateStyle? If it's OK to regurgitate "11-12-2007" as "2007-12-11",
I'm not clear on why adjusting timezone isn't OK.

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Treat 2007-10-10 14:34:31 Re: [COMMITTERS] pgsql: Added the Skytools extended transaction ID module to contrib as
Previous Message Bruce Momjian 2007-10-10 14:29:43 Re: Skytools committed without hackers discussion/review