Re: [GENERAL] timestamp skew during 7.4 -> 8.2 upgrade
On Thu, Aug 09, 2007 at 05:27:55PM +, 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 <[EMAIL PROTECTED]> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] timestamp skew during 7.4 -> 8.2 upgrade
Louis-David Mitterrand <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] timestamp skew during 7.4 -> 8.2 upgrade
On Fri, Aug 10, 2007 at 04:59:52PM -0400, Tom Lane wrote: > Karsten Hilbert <[EMAIL PROTECTED]> 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, ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] timestamp skew during 7.4 -> 8.2 upgrade
Karsten Hilbert <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] timestamp skew during 7.4 -> 8.2 upgrade
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] timestamp skew during 7.4 -> 8.2 upgrade
On Thu, Aug 09, 2007 at 10:49:38AM -0500, Scott Marlowe wrote: > On 8/9/07, Louis-David Mitterrand > <[EMAIL PROTECTED]> 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
Re: [GENERAL] timestamp skew during 7.4 -> 8.2 upgrade
On 8/9/07, Louis-David Mitterrand <[EMAIL PROTECTED]> 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. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] timestamp skew during 7.4 -> 8.2 upgrade
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, ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
