On Thu, Aug 09, 2007 at 10:49:38AM -0500, Scott Marlowe wrote:
> On 8/9/07, Louis-David Mitterrand
> <vindex+lists-pgsql-general@apartia.org> wrote:
> > Hi,
> >
> > After our 7.4 to 8.2 upgrade using debian tools, we realized that some
> > of our timestamps with tz had shifted:
> >
> > For example '2007-04-01 00:00:00+02' became '2007-03-31 23:00:00+01'
> > which is on a different month. Some of our applications were severely
> > disturbed by that.
> >
> > Has anyone noticed that? Is there a way that would could have avoided
> > it?
>
> Since timestamptz is stored as a GMT time, and then an offset is
> applied on retrieval, I'd guess that with 8.2 you're using up to date
> timezone files, and with 7.4 they were out of date and therefore
> returning the wrong time. I.e. they had the wrong offset for a given
> date.
>
> Not sure how you could avoid it off the top of my head, besides
> keeping your 7.4 db tz data up to date.
I sheepishly admit I never really understood the timestamp_tz mechanism
in postgres, until that issue reared its head.
So if I understand correctly, a timestamp_tz is UTC time shifted
according to the host's timezone configuration? For example if I
travel with my server and cross several timezones, my timestamp_tz's
will display a different time (provided I run the tzselect utility in
Linux) ?
Thanks,