From: | Louis-David Mitterrand <vindex+lists-pgsql-general(at)apartia(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: timestamp skew during 7.4 -> 8.2 upgrade |
Date: | 2007-08-10 08:11:29 |
Message-ID: | 20070810081129.GA27594@apartia.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Aug 09, 2007 at 10:49:38AM -0500, Scott Marlowe wrote:
> On 8/9/07, Louis-David Mitterrand
> <vindex+lists-pgsql-general(at)apartia(dot)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,
From | Date | Subject | |
---|---|---|---|
Next Message | Michal Paluchowski | 2007-08-10 08:37:16 | Multiple operations on single rule, revisited |
Previous Message | hubert depesz lubaczewski | 2007-08-10 08:00:47 | Re: [SQL] Using function like where clause |