Bug #695: Wrong Timezone offset (like bug#672)

N. Fung (php-jp(at)typhoon(dot)co(dot)jp) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
Wrong Timezone offset (like bug#672)

Long Description
function datetime() and extract() returns wrong timezone offset values. This is present in v7.2.1 but NOT in v7.0.3:

In v7.0.3:

% perl -le 'print time ;'
1024903188
% psql template1
Welcome to psql, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

template1=# select datetime(1024903188) \g
timestamp
------------------------
2002-06-24 16:19:48+09
(1 row)

template1-# \q
% date
Mon Jun 24 16:20:17 JST 2002

In v7.2.1:

perl -le 'print time'
1024903424
% psql template1
Welcome to psql, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

template1=# select datetime(1024903424) \g
timestamp
---------------------
2002-06-23 22:23:44
(1 row)

template1=# \q
% date
Mon Jun 24 16:24:12 JST 2002

% psql template1
Welcome to psql, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

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)

---

Although I am in JST (UTC+9) postgresql is telling me the I'm UTC-9.

--end of report---

Sample Code

No file was uploaded with this report

pgsql-bugs(at)postgresql(dot)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