pgsql-bugs@postgresql.org writes:
> function datetime() and extract() returns wrong timezone offset values. This is present in v7.2.1 but NOT in v7.0.3:
> template1=# select datetime(1024903424) \g
> timestamp
> ---------------------
> 2002-06-23 22:23:44
> (1 row)
This part is not a bug --- datetime is considered an obsolescent name for
type timestamp, which is migrating in the direction of timestamp without
time zone per SQL spec. In fact, the name datetime won't be recognized
at all anymore in 7.3. Try it with the correct type name:
test72=# set TimeZone to 'JST-9';
SET VARIABLE
test72=# select timestamptz(1024903424);
timestamptz
------------------------
2002-06-24 16:23:44+09
(1 row)
> template1=# select now() \g
> now
> ------------------------------
> 2002-06-24 16:30:09.99867+09
> (1 row)
> template1=# select extract(timezone_hour from now()) \g
> date_part
> -----------
> -9
> (1 row)
This perhaps is a bug. Although SQL92 is ambiguous, SQL99 seems pretty
clear that the timezone is to be interpreted as local time minus UTC.
For example, SQL99 part 2 section 4.7 saith:
For the convenience of users, whenever a datetime value with time
zone is to be implicitly derived from one without (for example,
in a simple assignment operation), SQL assumes the value without
time zone to be local, subtracts the default SQL-session time zone
displacement from it to give UTC, and associates that time zone
displacement with the result.
Conversely, whenever a datetime value without time zone is to be
implicitly derived from one with, SQL assumes the value with time
zone to be UTC, adds the time zone displacement to it to give local
time, and the result, without any time zone displacement, is local.
So it looks to me like timezone offsets should be negative in the
western hemisphere and positive in the eastern --- which agrees with
how we display timezone offset in timestamptz output, but
extract(timezone_hour) is doing it the other way round. Thomas,
what do you think?
regards, tom lane