Re: Timezone database changes

Trevor Talbot wrote:
> I wrote:
> > 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.
>
> 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...

Do we need additional documention about this?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +