Re: timestamp skew during 7.4 -> 8.2 upgrade
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.
Import Notes
Reply to msg id not found: 20070809172653.GA5235@apartia.frReference msg id not found: 20070809172653.GA5235@apartia.fr
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,
On Fri, Aug 10, 2007 at 10:11:29AM +0200, Louis-David Mitterrand wrote:
So if I understand correctly, a timestamp_tz is ...
... stored as UTC in the backend
... sent to clients shifted by whatever timezone was
requested by the client by one of several mechanisms:
- "set timezone to ..." used by the client
- "select ... at time zone ..." used by the client
- the server timezone if neither of the above is used
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) ?
Yes, unless the client tells the server to send them shifted
to a different timezone (see above).
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
On Fri, Aug 10, 2007 at 10:11:29AM +0200, Louis-David Mitterrand wrote:
So if I understand correctly, a timestamp_tz is ...
... stored as UTC in the backend
... sent to clients shifted by whatever timezone was
requested by the client by one of several mechanisms:
- "set timezone to ..." used by the client
- "select ... at time zone ..." used by the client
- the server timezone if neither of the above is used
The other point to be clear on is that the "shifting" is done according
to whatever timezone rule files the server currently has. Since
politicians keep changing daylight-savings rules, the same UTC date/time
might be displayed differently after an update of the relevant rule
file.
regards, tom lane
On Fri, Aug 10, 2007 at 04:59:52PM -0400, Tom Lane wrote:
Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
On Fri, Aug 10, 2007 at 10:11:29AM +0200, Louis-David Mitterrand wrote:
So if I understand correctly, a timestamp_tz is ...
... stored as UTC in the backend
... sent to clients shifted by whatever timezone was
requested by the client by one of several mechanisms:- "set timezone to ..." used by the client
- "select ... at time zone ..." used by the client
- the server timezone if neither of the above is usedThe other point to be clear on is that the "shifting" is done according
to whatever timezone rule files the server currently has. Since
politicians keep changing daylight-savings rules, the same UTC date/time
might be displayed differently after an update of the relevant rule
file.
(I am located in Paris, GMT+2, using debian unstable)
When using "date" here is the output on the server where the postgresql
upgrade (or more likely that's server's subsequent misconfiguration)
changed our timestamps:
uruk:~# date
Sat Aug 11 10:50:46 CEST 2007
uruk:~# date --utc
Sat Aug 11 08:50:49 UTC 2007
uruk:~#
and:
uruk:~# tzconfig
Your current time zone is set to Europe/Paris
But, I found something fishy that particular server:
uruk:~# hwclock
Sat 11 Aug 2007 10:47:36 AM CEST -0.630123 seconds
uruk:~# hwclock --utc
Sat 11 Aug 2007 12:47:39 PM CEST -0.600430 seconds
Whereas on my other servers "hwclock --utc" displays the same time
(is that normal?):
zenon:~# hwclock
Sat 11 Aug 2007 10:50:21 AM CEST -0.015345 seconds
zenon:~# hwclock --utc
Sat 11 Aug 2007 10:50:24 AM CEST -0.000235 seconds
Is postgres using the same time reference as "hwclock" or "date" ?
Thanks,
Louis-David Mitterrand <vindex+lists-pgsql-general@apartia.org> writes:
But, I found something fishy that particular server:
uruk:~# hwclock
Sat 11 Aug 2007 10:47:36 AM CEST -0.630123 seconds
uruk:~# hwclock --utc
Sat 11 Aug 2007 12:47:39 PM CEST -0.600430 seconds
If this is PC-type hardware, I'd guess that something is confused about
whether the hardware clock is running in UTC or local time.
Is postgres using the same time reference as "hwclock" or "date" ?
I'd expect PG to get the same results as "date". I have no idea what
"hwclock" is really doing.
regards, tom lane