Re: Timezone database changes

On 10/8/07, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> I had a thought a week ago. If we update the time zone database for
> future dates, and you have a future date/time stored, doesn't the time
> change when the time zone database changes.
>
> For example if I schedule an appointment in New Zealand for 10:00a and
> we change the time zone database so that date is now daylight savings,
> doesn't the time change to display as 9 or 11am? That seems pretty bad.

As a general rule, when you're doing planning or calendar type
applications where times need to be treated in local time, you never
store them in any other form (such as UTC). If you need to work with
multiple zones, you also store the timezone and do explicit
conversions on demand. In database terms, that means using "timestamp
without time zone" and some other column for the zone.

Put another way, when the authoritative reference is local time and
not absolute time, you don't use absolute time :)

I'm sure this trips up a lot of people, but it's S.O.P. for any
environment. OS services have the same caveats, and I've seen desktop
apps make this mistake and have to correct it later. (PostgreSQL
actually provides better support for time zones than some
environments. I've seen some use the current offset for conversions
of all times, which utterly breaks in the face of DST; others take DST
into account, but using the current year's DST rules only.)

It might be worth trying to document for PostgreSQL-using people to
find, but I don't see any need for behavior changes. Or anything
practical that could be done, for that matter.