On 10/8/07, Bruce Momjian <[EMAIL PROTECTED]> 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. ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend