Re: Timezone database changes

Trevor Talbot wrote:
> I wrote:
> > On 10/8/07, Bruce Momjian <bruce@momjian.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@momjian.us>        http://momjian.us EnterpriseDB
http://postgres.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +