timestamp skew during 7.4 -> 8.2 upgrade

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.

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?

Thanks,

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,

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(dot)Hilbert(at)gmx(dot)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(dot)Hilbert(at)gmx(dot)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.

(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(at)apartia(dot)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

On Thu, Aug 09, 2007 at 05:27:55PM +0000, Louis-David Mitterrand 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.

Youv've got the answer to your question, but I wonder if your app
really wanted just a "date" rather than a whole timestamp, which avoids
the issue entirely.

As for the difference between timestamp with/without timezone, it
depends on your usage. If the data represents an actual instant in
time, that you want to have rotated to the local time of the person
viewing, you need "with". If the data represents what the clock on the
wall said at the moment it happened and you don't want it rotated, no
matter what, you need "without".

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.