Re: [HACKERS] [GENERAL] Are we backwards on the sign of timezones?
On 4 Jul 2003 at 23:22, Tom Lane wrote: Dan Langille [EMAIL PROTECTED] writes: Now that my NZ server is up and running: template1=# select now(); 2003-07-05 12:47:15.444535+12 That doesn't look backwards to me. Try EXTRACT(timezone_hour from now()); The timestamp I/O routines are using what I think is the correct sign. EXTRACT() is at variance. So is SET TIMEZONE with a numeric offset. select now(), extract(timezone_hour from now()); now | date_part ---+--- 2003-07-05 23:15:09.760771+12 | -12 Yep, I'd say that should return +12, not -12. -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] [GENERAL] Are we backwards on the sign of timezones?
On 3 Jul 2003 at 13:18, Tom Lane wrote: Comments? The message headers for this email contained: Received: from www.postgresql.com (www.postgresql.com [64.117.225.209]) by m20.unixathome.org (Postfix) with ESMTP id 77EC27A69 for [EMAIL PROTECTED]; Thu, 3 Jul 2003 15:33:13 -0400 (EDT) My mail server is m20.unixathome.org and yes, it's four hours behind GMT. My PG server in Vancouver, shows this: # select current_time; timetz 12:36:43.579071-07 Which is three hours west of here (Ottawa).I was going to check my NZ server, but it's in the process of upgrading PG.. -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [GENERAL] Are we backwards on the sign of timezones?
I think you are right about this. When people look up a timezone, what we see is the offset to UTC. UTC + (offset) should equal current time. We should be consistent. And since the timestamp with timezone is relatively recent, maybe it will not be so painful to change the behaviour of extract() and date_part() to be consistent with the display of timezones. I don't think that many people do use extract or date part to fuss with the timezone. But that is just my experience. Anyone else who uses extract() and date_part() with timezones should weigh in their opinions. elein On Thursday 03 July 2003 10:18, Tom Lane wrote: Currently, the extract(timezone_hour ...) and extract(timezone_minute ...) constructs (also the equivalent date_part() calls) return positive values for timezones west of Greenwich, and negative values for timezones east of Greenwich. While the SQL92 spec was quite vague on the subject of the signs of timezone displacements, SQL99 seems to be pretty clear that Local time is equal to UTC (Coordinated Universal Time) plus the time zone displacement, which would mean that positive displacements correspond to zones east of Greenwich. Another point in favor of this interpretation is that the spec defines the legal range of displacement as -12:59 to +13:00, which is clearly intended to accommodate New Zealand Daylight Time (13 hours ahead of UTC) ... so NZDT has to be a positive offset not a negative one. Interestingly, this is also the sign convention used by the timestamptz and timetz I/O routines, which are certainly much more heavily used than EXTRACT(). The only other place I can find that uses west-is-positive convention is the code for SET TIMEZONE with a direct numeric timezone offset. I think we got this wrong as a result of misreading SQL92, and we ought to change EXTRACT() and SET/SHOW TIMEZONE to use the same sign convention as timestamp input/display use. Comments? Can anyone confirm which sign is used by other DBMSes? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- = [EMAIL PROTECTED] Database Consulting www.varlena.com PostgreSQL General Bitshttp:/www.varlena.com/GeneralBits/ Free your mind the rest will follow -- en vogue ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [GENERAL] Are we backwards on the sign of timezones?
On 3 Jul 2003 at 13:18, Tom Lane wrote: Comments? Now that my NZ server is up and running: template1=# select now(); now --- 2003-07-05 12:47:15.444535+12 That doesn't look backwards to me. Perhaps I don't understand the problem. After rereading your original post: Local time is equal to UTC (Coordinated Universal Time) plus the time zone displacement, In the above, the local time is 2003-07-05 12:47:15.444535. UTC would be 2003-07-05 00:47:15.444535. To which we add +12 hours to get local time. That appears to be consistent with the SQL99 spec. -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] Are we backwards on the sign of timezones?
Dan Langille [EMAIL PROTECTED] writes: Now that my NZ server is up and running: template1=# select now(); 2003-07-05 12:47:15.444535+12 That doesn't look backwards to me. Try EXTRACT(timezone_hour from now()); The timestamp I/O routines are using what I think is the correct sign. EXTRACT() is at variance. So is SET TIMEZONE with a numeric offset. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
