On Thu, Aug 09, 2007 at 10:49:38AM -0500, Scott Marlowe wrote: > On 8/9/07, Louis-David Mitterrand > <vindex+lists-pgsql-general@xxxxxxxxxxx> 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, ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
