Re: Timezone database changes

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...