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
