Re: [GENERAL] Daylight savings time confusion
Tom, You said, It seems to me that you're not entirely understanding how timestamps work in Postgres. That is an understatement! Thank you very much for your explanation. I have forwarded it to the other members of my development group, with my suggestion that we follow your ideas for future projects. I am not sure how easy it will be to retrofit existing projects, but I am sure it should be done. One question: We have customers all over the world. It would be best if we could rely on the operating system (usually Windows Server 2003) to tell us what time zone we're in, rather than asking for a specific timezone when we want to know a wallclock time. Is that possible? If not, it's not that big a deal because our database includes a table named system_info that contains a single record describing the customer's environment. We could just add a timezone field to that table. But how would we do that? What data type should that column have, and what would a query look like that converts a time from UTC to local time based on that field? As I was typing that question, I think I came up with the answer: the question is irrelevant. The reason for having a field to store times in UTC is so that intervals between times can be calculated without worrying about daylight savings time. But Postgres will take the timezone into account when calculating intervals, so there is no reason at all to store a UTC version of the time. And, as you pointed out, storing the same value twice is horrible database design. RobR -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Daylight savings time confusion
On 22 Mar 2010, at 14:08, Rob Richardson wrote: One question: We have customers all over the world. It would be best if we could rely on the operating system (usually Windows Server 2003) to tell us what time zone we're in, rather than asking for a specific timezone when we want to know a wallclock time. Is that possible? If Usually that timezone is set in the client program that connects to the database. If that program lives on a central location instead of at your customers' then you may be able to determine their timezone from the client they are using upstream and pass it along to the database server. For example, web browsers often pass along what timezone they're connecting from, so you may be able to set the client timezone based on that information. A drawback of storing a clients' timezone at the server is that you would be wrong if they are connecting from another location than they usually do, for example while at a conference in a different country. If you leave determining the timezone up to them you can't ever be wrong ;) Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4ba789e510411783369698! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Daylight savings time confusion
Tom Lane wrote: If my guesses are correct, then the minimum change to avoid this type of problem in the future is to change UTCTimestamp to be declared as timestamp WITHOUT time zone, so that you don't get two extra zone rotations in there. However, I would strongly suggest that you rethink how you're storing the data altogether. Two columns that represent the identical item of information is not good database design according to any theory I've ever heard. What I'd store is a single fire_date column that is of type timestamp with time zone and is just assigned directly from current_timestamp without any funny business. Internally it is UTC and completely unambiguous. Subsequently you can read it out in any time zone you want, either by setting TimeZone appropriately or by using the AT TIME ZONE construct to do a one-time conversion. And possibly store the original timezone as a separate column, if that information is of any value. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Daylight savings time confusion
Greetings! Our database monitors the progression of steel coils through the annealing process. The times for each step are recorded in wallclock time (US eastern time zone for this customer) and in UTC time. During standard time, the difference will be 5 hours, and during daylight savings time the difference will be 4 hours. I just looked at the record for a charge for which heating started just after 9:00 Saturday night, less than 3 hours before the change to daylight savings time. The UTC time stored for this event is six hours later! The function that writes these times first stores the UTC time in a variable named UTCTimestamp: select into UTCTimestamp current_timestamp at time zone 'UTC'; Then, later in the function, the two times get written into the record (along with some other stuff): update charge set status=ChargeStatus,fire_date=current_timestamp, fire_date_utc=UTCTimestamp, fire_user=FurnaceTender, updated_by=UserId,updated_date=current_timestamp where charge=ChargeNum; Can someone explain why fire_date is 2010-03-13 21:39:51.744 and fire_date_utc is 2010-03-14 03:39:51.744 for this record? There is another charge that began firing five and a half hours before the DST switch. The difference between its fire_date and fire_date_utc times is five hours, as expected. RobR -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Daylight savings time confusion
On 3/15/2010 2:40 PM, Rob Richardson wrote: Greetings! Our database monitors the progression of steel coils through the annealing process. The times for each step are recorded in wallclock time (US eastern time zone for this customer) and in UTC time. During standard time, the difference will be 5 hours, and during daylight savings time the difference will be 4 hours. I just looked at the record for a charge for which heating started just after 9:00 Saturday night, less than 3 hours before the change to daylight savings time. The UTC time stored for this event is six hours later! The function that writes these times first stores the UTC time in a variable named UTCTimestamp: select into UTCTimestamp current_timestamp at time zone 'UTC'; Then, later in the function, the two times get written into the record (along with some other stuff): update charge set status=ChargeStatus,fire_date=current_timestamp, fire_date_utc=UTCTimestamp, fire_user=FurnaceTender, updated_by=UserId,updated_date=current_timestamp where charge=ChargeNum; Can someone explain why fire_date is 2010-03-13 21:39:51.744 and fire_date_utc is 2010-03-14 03:39:51.744 for this record? There is another charge that began firing five and a half hours before the DST switch. The difference between its fire_date and fire_date_utc times is five hours, as expected. RobR My first thought is the server is using libraries that don't know the DST was brought forward 3 weeks earlier than last year, its clock is all confused. i would check the time on Postgresql Server making sure it read out correctly. below was run on pg 8.4 windows 2008 server Select current_timestamp, current_timestamp at time zone 'UTC'; 2010-03-15 16:43:11.382-04;2010-03-15 20:43:11.382 All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored. CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately. Thank you. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Daylight savings time confusion
Thanks for the try, Justin, but that doesn't seem to be the problem. The query generates the same results on my customer's machine. Besides, I think your theory would only hold up if there were two machines involved. There aren't. RobR -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Daylight savings time confusion
Rob Richardson wrote: Greetings! ... I just looked at the record for a charge for which heating started just after 9:00 Saturday night, less than 3 hours before the change to daylight savings time. The UTC time stored for this event is six hours later! The function that writes these times first stores the UTC time in a variable named UTCTimestamp: select into UTCTimestamp current_timestamp at time zone 'UTC'; Then, later in the function, the two times get written into the record (along with some other stuff): update charge set status=ChargeStatus,fire_date=current_timestamp, fire_date_utc=UTCTimestamp, fire_user=FurnaceTender, updated_by=UserId,updated_date=current_timestamp where charge=ChargeNum; Can someone explain why fire_date is 2010-03-13 21:39:51.744 and fire_date_utc is 2010-03-14 03:39:51.744 for this record? There is another charge that began firing five and a half hours before the DST switch. The difference between its fire_date and fire_date_utc times is five hours, as expected I think you are shooting yourself in the foot with the different timestamp columns. Time is time is time and you only need one column to represent it. I think the problems were masked until the time-zone change. (Trust me, I'm having my own fun, today. Try date -d yesterday between midnight and 1am the day after springing forward and you get the 11pm hour Saturday but date -d '0015 2010-03-15 -1 day' gives fifteen minutes past midnight on the 14th.) It is a bit difficult to trace everything without seeing your full functions and column types but I believe that the first issue is that when you specify the timezone, the result does not include the time-zone offset (timestamp without tz). Note that there is no -00 (or +00) and there isn't one regardless of zone: select now(),now() at time zone 'UTC' as utc, now() at time zone 'America/New_York' as ny; -[ RECORD 1 ]- now | 2010-03-15 15:34:52.3342-07 utc | 2010-03-15 22:34:52.3342 ny | 2010-03-15 18:34:52.3342 Now see what happens if you run: select current_timestamp, (select current_timestamp at time zone 'UTC')::timestamptz ; -[ RECORD 1 ]-- now | 2010-03-15 15:39:44.594979-07 timestamptz | 2010-03-15 22:39:44.594979-07 Two timestamptz columns offset by 7 hours. (Really offset - they are both displayed in Pacific Daylight Time). The second issue is that depending on which of your columns/variables are with or without the zone information and how you do your calculations, you could easily end up with a situation where your current time is Standard so your program knows the correct offset to be 5 hours which you add to a 9pm timestamptz. Given the missing hour, 9pm plus 5 hours gets you to 3am. But if you are mix-and-matching timestamps with and without time-zone you are in for some interesting problems. Finally, beware that time handling has been updated across PG versions. For example, select now() - '1 day'::interval works differently in, 7.4 (if run early Monday after a time change you will end up with late Saturday) than in 8.4 (you get the current time of day on Sunday). So if you take the difference between those two timestamps in 7.4 it is 24 hours but in 8.4 it is 23 hours. A better approach is to store the fully-qualified timestamp in a single column of type timestamptz instead of duplicated columns that are supposed to represent the same point in time (but different zones). Then display that one column in whatever timezone(s) you want: select now() as local, now() at time zone 'America/New_York' as eastern, now() at time zone 'CST6CDT' as central, now() at time zone 'Chile/Continental' as chile, now() at time zone 'Africa/Addis_Ababa' as ethiopia; -[ RECORD 1 ]--- local| 2010-03-15 15:47:01.644575-07 eastern | 2010-03-15 18:47:01.644575 central | 2010-03-15 17:47:01.644575 chile| 2010-03-15 18:47:01.644575 ethiopia | 2010-03-16 01:47:01.644575 Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Daylight savings time confusion
On 03/15/2010 12:40 PM, Rob Richardson wrote: Greetings! Our database monitors the progression of steel coils through the annealing process. The times for each step are recorded in wallclock time (US eastern time zone for this customer) and in UTC time. During standard time, the difference will be 5 hours, and during daylight savings time the difference will be 4 hours. I just looked at the record for a charge for which heating started just after 9:00 Saturday night, less than 3 hours before the change to daylight savings time. The UTC time stored for this event is six hours later! First, the time change occurs at 2:00 am Sunday morning which is 5 five hours after 9:00 pm Saturday. Second the timestamps below show a start time of 39 minutes after 9 which a little more than just after:) Are you sure about the time? The function that writes these times first stores the UTC time in a variable named UTCTimestamp: select into UTCTimestamp current_timestamp at time zone 'UTC'; Then, later in the function, the two times get written into the record (along with some other stuff): update charge set status=ChargeStatus,fire_date=current_timestamp, fire_date_utc=UTCTimestamp, fire_user=FurnaceTender, updated_by=UserId,updated_date=current_timestamp where charge=ChargeNum; Can someone explain why fire_date is 2010-03-13 21:39:51.744 and fire_date_utc is 2010-03-14 03:39:51.744 for this record? Some other process updated either field? There is another charge that began firing five and a half hours before the DST switch. The difference between its fire_date and fire_date_utc times is five hours, as expected. RobR -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Daylight savings time confusion
Rob Richardson rob.richard...@rad-con.com writes: Our database monitors the progression of steel coils through the annealing process. The times for each step are recorded in wallclock time (US eastern time zone for this customer) and in UTC time. During standard time, the difference will be 5 hours, and during daylight savings time the difference will be 4 hours. It seems to me that you're not entirely understanding how timestamps work in Postgres. The above is quite unnecessary, and the way that you're computing the data to store looks wrong too. I think the problem is that you are inserting unnecessary (and incorrect) conversions because of sloppiness about data types. You started with (to simplify matters) 9:39pm last Saturday: # select '2010-03-13 21:39 EST'::timestamptz; timestamptz 2010-03-13 21:39:00-05 (1 row) Now what this is under the hood is a *UTC time*. The fact that I entered it as a time with respect to EST zone doesn't change that; it got rotated to UTC internally. The display as EST doesn't change it either; that's because the internal value is rotated back to my TimeZone setting (EST5EDT) for display. So the actual internal value is equivalent to 2010-03-14 02:39:00 UTC. (In your problem case, that was what you got from current_timestamp, but we can experiment with this manually entered value instead.) You then did this: select into UTCTimestamp current_timestamp at time zone 'UTC'; What the AT TIME ZONE expression produces is a timestamp WITHOUT time zone value, which will be '2010-03-14 02:39:00' without reference to any particular time zone: # select '2010-03-13 21:39 EST'::timestamptz at time zone 'UTC'; timezone - 2010-03-14 02:39:00 (1 row) Now at this point I have to guess, since you didn't show us the declared data types of any of the variables involved, but I'm going to guess that the local variable UTCTimestamp is declared as timestamp WITH time zone (timestamptz) whereas the fire_date and fire_date_utc columns are timestamp WITHOUT time zone. Since the result of the AT TIME ZONE construct is timestamp WITHOUT time zone, it will have to be converted to timestamp WITH time zone to be stored into UTCTimestamp. And since the value has no attached time zone, the conversion process will assume that it's relative to the zone specified by TimeZone. So that means it's interpreted as 2010-03-14 02:39:00 in EST5EDT. And there's a bit of a problem with that: since we jumped from 02:00 to 03:00 local time, there *was* no instant when a properly functioning clock would have read 02:39 local time. You could make an argument for throwing an error here, but what the timestamp input routine actually does is to assume that local standard time was meant. So the result is the equivalent of 07:39 UTC (five-hour offset from the given time). If I do this by hand I get # select '2010-03-14 02:39:00'::timestamptz; timestamptz 2010-03-14 03:39:00-04 (1 row) The display is 03:39 EDT, which is what an east-coast clock would actually have read at 07:39 UTC. Remember that the internal value is just UTC; the rotation to 03:39 is an I/O or conversion behavior. And then lastly you stored this value into a timestamp WITHOUT time zone column. That means it gets rotated to the TimeZone zone, as if for display. So what went into the fire_date_utc column is '2010-03-14 03:39:00', sans any identifying information that would have clarified what this was supposed to mean. Meanwhile, your fire_date column was set directly from current_timestamp without any intermediate shenanigans, so what it got was 02:39 UTC rotated just once to local time, producing 21:39 of the previous day as expected. If my guesses are correct, then the minimum change to avoid this type of problem in the future is to change UTCTimestamp to be declared as timestamp WITHOUT time zone, so that you don't get two extra zone rotations in there. However, I would strongly suggest that you rethink how you're storing the data altogether. Two columns that represent the identical item of information is not good database design according to any theory I've ever heard. What I'd store is a single fire_date column that is of type timestamp with time zone and is just assigned directly from current_timestamp without any funny business. Internally it is UTC and completely unambiguous. Subsequently you can read it out in any time zone you want, either by setting TimeZone appropriately or by using the AT TIME ZONE construct to do a one-time conversion. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Daylight Savings Time
Hello List:
I need to know if there is a convienient way of establishing whether DST is
active within a function dealing with adjusting timestamps to other time
zones. The problem is that if I have the following timestamp:
'04/21/2006 17:05 EDT'
and I use the timezone() function in the following manner:
return (timezone ('CST', '04/21/2006 17:05 EDT')
I get a two hour difference in time. Note that neither of the two arguments
are hard coded as this example. The CST value is stored in the customer
profile because that is their time zone and the timestamp is generated from
argeuments passed into the function.
This is:
rnd=# select version();
version
--
PostgreSQL 7.4.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
20030502 (Red Hat Linux 3.2.3-49)
TIA
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] Daylight Savings Time
Terry Lee Tucker [EMAIL PROTECTED] writes:
I need to know if there is a convienient way of establishing whether DST is
active within a function dealing with adjusting timestamps to other time
zones. The problem is that if I have the following timestamp:
'04/21/2006 17:05 EDT'
and I use the timezone() function in the following manner:
return (timezone ('CST', '04/21/2006 17:05 EDT')
I get a two hour difference in time.
Perhaps you should be using a DST-aware timezone specification? Since
8.1 you could do
regression=# select timezone ('CST6CDT', '04/21/2006 17:05 EDT'::timestamptz);
timezone
-
2006-04-21 16:05:00
(1 row)
regards, tom lane
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] Daylight Savings Time
On Friday 21 April 2006 05:47 pm, Tom Lane [EMAIL PROTECTED] thus
communicated:
-- Terry Lee Tucker [EMAIL PROTECTED] writes:
-- I need to know if there is a convienient way of establishing whether
DST is -- active within a function dealing with adjusting timestamps to
other time -- zones. The problem is that if I have the following
timestamp:
-- '04/21/2006 17:05 EDT'
-- and I use the timezone() function in the following manner:
-- return (timezone ('CST', '04/21/2006 17:05 EDT')
-- I get a two hour difference in time.
--
-- Perhaps you should be using a DST-aware timezone specification? Since
-- 8.1 you could do
--
-- regression=# select timezone ('CST6CDT', '04/21/2006 17:05
EDT'::timestamptz); -- timezone
-- -
-- 2006-04-21 16:05:00
-- (1 row)
--
--
-- regards, tom lane
--
Thanks for the reply Tom. We will be upgrading to version 8.x hopefully in
August. I can implement a work around until then. So, when we can upgrade, we
will change the timezone specification in the customer profiles to the
DST-aware specification, and we will be set.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Daylight Savings Time handling on persistent connections
On Sunday 31 October 2004 11:44 am, Tom Lane wrote: Randall Nortman [EMAIL PROTECTED] writes: Ah, I see now. PostgreSQL is behaving a bit differently than I expected. The timestamp string above is ambiguous in the timezone US/Eastern -- it could be EST or EDT. I was expecting PostgreSQL to resolve this ambiguity based on the current time when the SQL statement is processed I think this would be a very bad thing for it to do. It might seem to make sense for a timestamp representing now, but as soon as you consider a timestamp that isn't now it becomes a sure way to shoot yourself in the foot. Would it help to add the PG locale TZ to the insert statement? For example the following queries return the TZ as text. select to_char(now(),'tz'); to_char - pst select to_char(now()-'3 days'::interval,'tz'); to_char - pdt So the following might fix this particular situation: insert into sensor_readings_numeric (...) values (...,'2004-10-31 01:00:00 ' || to_char(now(),'tz'),...) I realize that it assumes that the data is being inserted at the time it was taken so a reading taken just before DST changes and inserted just after will be incorrect but it may work for this particular app. Of course the better solution is to have the application generate a fully-qualified timestamp with time zone. Generating all the timestamps in UTC and explicitly specifying that in the insert is probably the easiest way to go. Your queries will still have your local-appropriate TZ: select '2004-10-31 00:00:00+00'::timestamptz; timestamptz 2004-10-30 17:00:00-07 select '2004-11-01 00:00:00+00'::timestamptz; timestamptz 2004-10-31 16:00:00-08 Cheers, Steve ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Daylight Savings Time handling on persistent connections
On Sun, Oct 31, 2004 at 05:55:23PM -0500, Tom Lane wrote: [...] I'm inclined to think that rejecting impossible or ambiguous input without a zone is reasonable (and it would go along with the changes we made in 7.4 to tighten up datetime field order assumptions). But I don't want to take away the convenience of leaving off the zone altogether. One point here is that timestamp-to-timestamptz datatype conversion will be affected by whatever we choose. While it's easy to say reject it for data coming into a database, it's less easy to say that a coercion function should fail on some inputs it didn't use to fail on. What about letting the user decide on the behaviour through a config option? I really missed this when the integer parsing changed. The default could be to reject ambiguous input, allowing the user to choose the assumed zone if he wants to, in a global and per-connection basis. -- Vinko Vrsalovic el[|[EMAIL PROTECTED]|]vinko.cl ---(end of broadcast)--- TIP 3: 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] Daylight Savings Time handling on persistent connections
On Mon, Nov 01, 2004 at 01:57:38PM -0300, Vinko Vrsalovic wrote: On Sun, Oct 31, 2004 at 05:55:23PM -0500, Tom Lane wrote: One point here is that timestamp-to-timestamptz datatype conversion will be affected by whatever we choose. While it's easy to say reject it for data coming into a database, it's less easy to say that a coercion function should fail on some inputs it didn't use to fail on. What about letting the user decide on the behaviour through a config option? I really missed this when the integer parsing changed. The default could be to reject ambiguous input, allowing the user to choose the assumed zone if he wants to, in a global and per-connection basis. It's not that simple. In this case the conversion will now produce a different datatype, which means that perfectly valid pl/pgsql may now be invalid. Columns defined unique will now have a different criteria for uniqueness. Even how timestamps are stored will be different. By switching a config option, you may have just invalidated your entire database. For the parsing integer issue it may have worked, but this is another kettle of fish. I don't think you can do this as a simple switch, it would have to set during the initdb and not allowed to be changed afterwards. I don't know if that something that can be supported. Hope this helps, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpzcwYFj3X0U.pgp Description: PGP signature
Re: [GENERAL] Daylight Savings Time handling on persistent connections
On Mon, Nov 01, 2004 at 07:08:39PM +0100, Martijn van Oosterhout wrote: For the parsing integer issue it may have worked, but this is another kettle of fish. I don't think you can do this as a simple switch, it would have to set during the initdb and not allowed to be changed afterwards. I don't know if that something that can be supported. I suspected it wasn't that easy. Anyhow, I strongly believe that when no reasonable defaults can be deduced, the software should give the user the ability to decide what he wants to do. Of course technical (implementation, maintenance, etc.) issues are highly relevant and if it can't reasonably be done, well, tough luck, but I think (and I don't have a clue about the internals of PostgreSQL, so take this with two grains of salt) a solution such as the one you mention should be given consideration. -- Vinko Vrsalovic el[|[EMAIL PROTECTED]|]vinko.cl ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Daylight Savings Time handling on persistent connections
I assume I'm not the first person to have encountered this, but I couldn't find anything in the FAQ or on the mailing lists recently. My apologies if this is already documented somewhere... My application logs data to a Postgres table continuously (once every 15 seconds), maintaining a persistent connection. Each datum is logged with a time stamp (Postgres type timestamp with time zone). The application does not explicitly set the time zone, and does not specify it when inserting the records. So everything just defaults to the local time zone configured for the system, which is US/Eastern. This has been working fine all summer. Until this morning, of course, when DST ended and US/Eastern switched from GMT+4 to GMT+5. Everything logged fine up to 01:59 EDT (05:59 UTC). Then the clock ticked to 01:00 EST (06:00 UTC), and I got a unique constraint violation, because the database incorrectly computed that I was trying to insert another record at 01:00 EDT (05:00 UTC). I restarted the application when I noticed the problem this morning, and now everything is working correctly. My suspicion is that Postgres calculates the local offset from UTC only once per session, during session initialization. Therefore, it fails to notice when the local offset changes as a result of DST, causing the problem I just described. It's hard for me to test this, because I don't have a system I can freely muck with the clock on, but it would completely explain this behavior. Is this what's happening? Is it considered a bug? I can see making the case for not changing the offset mid-session, but in that case it should be explained more thoroughly in the documentation. In my case, I think I'll have my app convert all times to UTC before inserting them. This should avoid all such problems in the future. PostgreSQL version (client and server) is 7.4.5, on i686 Debian sarge. The client app is in python 2.3.4 using psycopg. Thanks, Randall Nortman ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Daylight Savings Time handling on persistent connections
Randall Nortman [EMAIL PROTECTED] writes: My suspicion is that Postgres calculates the local offset from UTC only once per session, during session initialization. This is demonstrably not so. We might be able to figure out what actually went wrong, if you would show us the exact commands your application issued. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Daylight Savings Time handling on persistent connections
On Sun, Oct 31, 2004 at 11:52:03AM -0500, Tom Lane wrote:
Randall Nortman [EMAIL PROTECTED] writes:
My suspicion is that Postgres calculates the local offset from UTC
only once per session, during session initialization.
This is demonstrably not so. We might be able to figure out what
actually went wrong, if you would show us the exact commands your
application issued.
I can't reproduce the error without messing up my clock, but from my
logs, here's the text of the SQL sent to the server:
insert into sensor_readings_numeric (sensor_id, reading_ts, reading,
min, max) values (3, '2004-10-31 01:00:00', 0.540602, 0.519071837254,
0.551811824539)
And this came back:
ERROR: duplicate key violates unique constraint sensor_readings_numeric_pkey
Table definition:
Table public.sensor_readings_numeric
Column | Type |
Modifiers
+--+-
sensor_id | integer | not null
reading_ts | timestamp with time zone | not null default ('now'::text)::timestamp(6)
with time zone
reading| numeric | not null
min| numeric |
max| numeric |
Indexes:
sensor_readings_numeric_pkey primary key, btree (reading_ts, sensor_id)
Foreign-key constraints:
$1 FOREIGN KEY (sensor_id) REFERENCES sensors(sensor_id)
I'll try to set up a system where I can play around with the clock to
see if I can reproduce the error, but it'll probably be a few days at
least before I can do that. There's no hurry for me, since this won't
happen again until next year.
Thanks,
Randall Nortman
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Daylight Savings Time handling on persistent connections
Randall Nortman [EMAIL PROTECTED] writes: I can't reproduce the error without messing up my clock, but from my logs, here's the text of the SQL sent to the server: insert into sensor_readings_numeric (sensor_id, reading_ts, reading, min, max) values (3, '2004-10-31 01:00:00', 0.540602, 0.519071837254, 0.551811824539) And this came back: ERROR: duplicate key violates unique constraint sensor_readings_numeric_pkey Hmm ... and you were generating that timestamp string how exactly? I suspect that you actually sent the same timestamp string twice, one hour apart, in which case I'd have to call this an application bug. You really need to include the timezone specification in order to have an unambiguous timestamp string. It doesn't have to be UTC as you previously suggested, but it does have to be labeled with the intended zone. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Daylight Savings Time handling on persistent connections
On Sun, Oct 31, 2004 at 12:47:31PM -0500, Tom Lane wrote: Randall Nortman [EMAIL PROTECTED] writes: I can't reproduce the error without messing up my clock, but from my logs, here's the text of the SQL sent to the server: insert into sensor_readings_numeric (sensor_id, reading_ts, reading, min, max) values (3, '2004-10-31 01:00:00', 0.540602, 0.519071837254, 0.551811824539) And this came back: ERROR: duplicate key violates unique constraint sensor_readings_numeric_pkey Hmm ... and you were generating that timestamp string how exactly? I suspect that you actually sent the same timestamp string twice, one hour apart, in which case I'd have to call this an application bug. You really need to include the timezone specification in order to have an unambiguous timestamp string. It doesn't have to be UTC as you previously suggested, but it does have to be labeled with the intended zone. Ah, I see now. PostgreSQL is behaving a bit differently than I expected. The timestamp string above is ambiguous in the timezone US/Eastern -- it could be EST or EDT. I was expecting PostgreSQL to resolve this ambiguity based on the current time when the SQL statement is processed -- if it's currently EST, then the server would assume that EST was intended, but if it's currently EDT, then it would assume EDT. If this were the case, my code would be correct -- yes, I tried to insert the same timestamp value twice, but the inserts were issued when my local timezone was in different offsets from UTC. But it appears that PostgreSQL always assumes EDT in this case, regardless of the current time? I can see that being a good idea, for its predictability. For example, a client running on a different host than the server might have its clock off by a few minutes; this could then cause the server to make a different assumption about the correct time zone than the client. Even running on the same host, a delay between the client issuing a command and the server processing it could cause this problem. So yeah, I see the wisdom of always specifying a time zone explicitly in the query. In my case, it will probably be easiest to specify UTC, because otherwise I have to figure out myself whether or not DST was in effect when the sensor reading was generated. In my code, in fact, timestamps are recorded as seconds since the epoch, in UTC, so it makes little sense to convert to local time anyway. Right now, psycopg (the python module I'm using for postgres access) is generating the timestamp string for me (via psycopg.TimestampFromTicks()). I just need to figure out how to get it to generate the string with an explicit time zone, which I'm sure is possible. And if not, I'll just generate the string myself. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Daylight Savings Time handling on persistent connections
Randall Nortman [EMAIL PROTECTED] writes: Ah, I see now. PostgreSQL is behaving a bit differently than I expected. The timestamp string above is ambiguous in the timezone US/Eastern -- it could be EST or EDT. I was expecting PostgreSQL to resolve this ambiguity based on the current time when the SQL statement is processed I think this would be a very bad thing for it to do. It might seem to make sense for a timestamp representing now, but as soon as you consider a timestamp that isn't now it becomes a sure way to shoot yourself in the foot. But it appears that PostgreSQL always assumes EDT in this case, regardless of the current time? Actually, the intended and documented behavior is that it should interpret an ambiguous time as local standard time (e.g., EST not EDT). That seems to be broken at the moment :-(, which is odd because I'm quite certain I tested it last time we touched the relevant subroutine. We have had varying and often platform-specific behaviors on this point in past releases, but in 8.0 it should be possible to ensure consistent results now that we are no longer at the mercy of the local libc's timezone code. Before I go off and try to fix it, does anyone have any objection to the rule interpret an ambiguous time as local standard time? This would normally mean picking the later of the two possible interpretations, which might be the wrong choice for some applications. (I notice that HPUX's cron is documented to choose the earlier interpretation in comparable situations.) In my code, in fact, timestamps are recorded as seconds since the epoch, in UTC, so it makes little sense to convert to local time anyway. Right now, psycopg (the python module I'm using for postgres access) is generating the timestamp string for me (via psycopg.TimestampFromTicks()). I just need to figure out how to get it to generate the string with an explicit time zone, which I'm sure is possible. And if not, I'll just generate the string myself. Actually, your best bet is to forgo the conversion altogether. The recommended way to get from a Unix epoch value to a timestamp is 'epoch'::timestamptz + N * '1 second'::interval For example: regression=# select 'epoch'::timestamptz + 1099251435 * '1 second'::interval; ?column? 2004-10-31 14:37:15-05 (1 row) Or you can do select 'epoch'::timestamptz + '1099251435 seconds'::interval; which saves a couple microseconds at execution but requires assembling the query string as a string. The latter is probably easy for your application, but if say you were extracting the numeric value from a database column, the former would be easier. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Daylight Savings Time handling on persistent connections
On Sun, Oct 31, 2004 at 02:44:51PM -0500, Tom Lane wrote: Randall Nortman [EMAIL PROTECTED] writes: Ah, I see now. PostgreSQL is behaving a bit differently than I expected. The timestamp string above is ambiguous in the timezone US/Eastern -- it could be EST or EDT. I was expecting PostgreSQL to resolve this ambiguity based on the current time when the SQL statement is processed I think this would be a very bad thing for it to do. It might seem to make sense for a timestamp representing now, but as soon as you consider a timestamp that isn't now it becomes a sure way to shoot yourself in the foot. Yes, I absolutely see your point. But it appears that PostgreSQL always assumes EDT in this case, regardless of the current time? Actually, the intended and documented behavior is that it should interpret an ambiguous time as local standard time (e.g., EST not EDT). That seems to be broken at the moment :-(, which is odd because I'm quite certain I tested it last time we touched the relevant subroutine. It certainly seems that way, but as I've said I can't reproduce the bug without mucking with my clock, which is not an option right now. But looking at the data which was generated overnight in UTC, I see continuous data all the way up to 05:59. If the server had started converting to EST at 01:00EDT, there would be a gap in the data from 05:00UTC to 06:00UTC as the server switched from a +4 offset to +5, and then data would have been logged with a timestamp one hour in the future through 06:59UTC, and then I would have gotten a unique constraint violation when the actual switch happened. Before I go off and try to fix it, does anyone have any objection to the rule interpret an ambiguous time as local standard time? This would normally mean picking the later of the two possible interpretations, which might be the wrong choice for some applications. (I notice that HPUX's cron is documented to choose the earlier interpretation in comparable situations.) I'm finding it hard to see how either way is likely to generate good results in *any* application, much less in a majority of applications. So in a way, perhaps the most correct thing to do would be to spit out an error if the timestamp is ambiguous. Any application which deals with timestamps in anything other than UTC should really be handling the disambiguation itself, because the server can't possibly know what the application means to do. Not generating an error is likely to allow an application bug to go unnoticed, especially if the database does not have a unique constraint on timestamps (as mine does). Then again, it's not up to the database to expose bugs in the client, so perhaps it's best to just stick with the current intended behavior of always choosing local standard time. Or maybe we should write our legislative representatives and get them to abolish DST. ;) Actually, your best bet is to forgo the conversion altogether. The recommended way to get from a Unix epoch value to a timestamp is 'epoch'::timestamptz + N * '1 second'::interval At first glance, that seems to me to be really inefficient, but that's just because my brain tends to associate verbosity in code with runtime overhead. In this case, it's probably just as fast as letting the Python library do the math required to convert the Unix timestamp to a date/time string. And if Postgres stores timestamps as some unit of time since an epoch, then it would be quite a bit more efficient. Of course, all these calculations happen in the blink of an eye, and I'm only logging data every 15 seconds, so I suppose it doesn't matter anyway. So thanks for the tip! That will be much easier and more reliable than the way I'm currently doing it. (I just hope that nobody ever gets the idea of changing the Unix epoch.) Thanks for all your help, Randall Nortman ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Daylight Savings Time handling on persistent connections
Randall Nortman [EMAIL PROTECTED] writes: On Sun, Oct 31, 2004 at 02:44:51PM -0500, Tom Lane wrote: Actually, the intended and documented behavior is that it should interpret an ambiguous time as local standard time (e.g., EST not EDT). I'm finding it hard to see how either way is likely to generate good results in *any* application, much less in a majority of applications. So in a way, perhaps the most correct thing to do would be to spit out an error if the timestamp is ambiguous. Any application which deals with timestamps in anything other than UTC should really be handling the disambiguation itself, because the server can't possibly know what the application means to do. Not generating an error is likely to allow an application bug to go unnoticed, especially if the database does not have a unique constraint on timestamps (as mine does). That line of argument leads directly to the conclusion that we shouldn't allow timezone-less input strings at all, since it's unlikely that anyone would code their app to append a timezone spec only during the two hours a year when it actually matters. And wouldn't you rather have had the problem pointed out immediately on testing the app, rather than waiting till 1AM on a fall Sunday morning to find out it's broken? However, I am not prepared to buy into requiring explicit TZ specs always... it's just too much of a PITA. For human users, there would be some value in acting this way, since it would serve to remind them of the issue only when it actually matters. Comments anyone? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Daylight Savings Time handling on persistent connections
On Sun, Oct 31, 2004 at 04:14:52PM -0500, Tom Lane wrote: That line of argument leads directly to the conclusion that we shouldn't allow timezone-less input strings at all, since it's unlikely that anyone would code their app to append a timezone spec only during the two hours a year when it actually matters. And wouldn't you rather have had the problem pointed out immediately on testing the app, rather than waiting till 1AM on a fall Sunday morning to find out it's broken? The only issue is storing a timezone-less timestamp into a field that is timestamp with timezone. Every other combination is easy to handle. If you're doing this, isn't it an indication that your field is the wrong type? It's just plain ambiguous no matter which way you put it. Assuming UTC would be almost as valid. We don't promote integers to other types of numbers, so maybe this shouldn't be allowed either. For human users, there would be some value in acting this way, since it would serve to remind them of the issue only when it actually matters. Comments anyone? Except that means your program will work all the time except for one or two hours per year where it breaks. Chances are your testing is not going to trip it... -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpxWYQLSfuMT.pgp Description: PGP signature
Re: [GENERAL] Daylight Savings Time handling on persistent connections
Martijn van Oosterhout [EMAIL PROTECTED] writes: On Sun, Oct 31, 2004 at 04:14:52PM -0500, Tom Lane wrote: That line of argument leads directly to the conclusion that we shouldn't allow timezone-less input strings at all, since it's unlikely that anyone would code their app to append a timezone spec only during the two hours a year when it actually matters. For human users, there would be some value in acting this way, since it would serve to remind them of the issue only when it actually matters. Comments anyone? Except that means your program will work all the time except for one or two hours per year where it breaks. Chances are your testing is not going to trip it... ISTM basically we have to make a tradeoff between convenience for human-driven data entry and reliability for program-driven data entry. Refusing TZ-less data input would certainly force programmers to write their programs more safely, but is it worth the inconvenience for interpreting human-generated input strings? I doubt it. We already allow a great variety of input syntaxes, some would say more than we should, in order to make the timestamp input converters useful for interpreting hand-entered strings. I'm inclined to think that rejecting impossible or ambiguous input without a zone is reasonable (and it would go along with the changes we made in 7.4 to tighten up datetime field order assumptions). But I don't want to take away the convenience of leaving off the zone altogether. One point here is that timestamp-to-timestamptz datatype conversion will be affected by whatever we choose. While it's easy to say reject it for data coming into a database, it's less easy to say that a coercion function should fail on some inputs it didn't use to fail on. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
