Thread: Are we backwards on the sign of timezones?
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
The date / time of your message at the top of my email client was: Thu, 03 Jul 2003 13:18:09 -0400 And most of my stuff is -0600 or -0700 and I live in Colorado. Every instance I've seen that shows the correction for me has been a -0600/-0700 depending on daylight savings. So, it looks like the standard for email / system time. I use timestamptz On Thu, 3 Jul 2003, 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 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match >
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/
"Dan Langille" <dan@langille.org> 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
On 4 Jul 2003 at 23:22, Tom Lane wrote:
> "Dan Langille" <dan@langille.org> 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/
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 <dan@langille.org>; 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/
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 > > -- ============================================================= elein@varlena.com Database Consulting www.varlena.com PostgreSQL General Bits http:/www.varlena.com/GeneralBits/ "Free your mind the rest will follow" -- en vogue