I wrote: > 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.
Actually, I'm used to knowing how PostgreSQL does it, but looking at things again I remember some confusion I had when first encountering the timestamp types. I don't know what the SQL Standard says; is the implication that "timestamp with time zone" actually stores the literal time and the zone it is associated with? (Would make more sense, given the name.) If that's true, then the current behavior is a bug^H^H^Hdocumented limitation. I still don't know of anything practical that could be done now, but... ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
