Обсуждение: BUG #14082: Unexpected time adjustment for output using "at time zone"

The following bug has been logged on the website:

Bug reference:      14082
Logged by:          Duncan Stokes
Email address:      duncan.stokes@eyemagnet.com
PostgreSQL version: 9.2.15
Operating system:   CentOS Linux release 7.2.1511 (Core)
Description:

Getting an unexpected output when using the "at time zone" function with
numerical (e.g. +12 or -12) time zones.

For the following table:
datetimetest=# CREATE TABLE datetimetest_log (id serial PRIMARY KEY UNIQUE
NOT NULL, datetime timestamptz(0) NOT NULL DEFAULT NOW(), comment text NOT
NULL);

For the following data:
datetimetest=# SELECT * FROM datetimetest_log;
 id |        datetime        | comment
----+------------------------+---------
  1 | 2016-04-11 09:51:35+12 | Test #1
  2 | 2016-04-11 09:51:37+12 | Test #2
(2 rows)


Get the following responses:
datetimetest=# SELECT datetime AT TIME ZONE INTERVAL '+12:00' FROM
datetimetest_log;
      timezone
---------------------
 2016-04-11 09:51:35
 2016-04-11 09:51:37
(2 rows)

datetimetest=# SELECT datetime AT TIME ZONE '+12' FROM datetimetest_log;
      timezone
---------------------
 2016-04-10 09:51:35   ** WRONG DATE FOR +12 ZONE **
 2016-04-10 09:51:37   ** WRONG DATE FOR +12 ZONE **
(2 rows)

datetimetest=# SELECT datetime AT TIME ZONE INTERVAL '-12:00' FROM
datetimetest_log;
      timezone
---------------------
 2016-04-10 09:51:35
 2016-04-10 09:51:37
(2 rows)

datetimetest=# SELECT datetime AT TIME ZONE '-12' FROM datetimetest_log;
      timezone
---------------------
 2016-04-11 09:51:35   ** WRONG DATE FOR -12 ZONE **
 2016-04-11 09:51:37   ** WRONG DATE FOR -12 ZONE **
(2 rows)

datetimetest=# SELECT datetime AT TIME ZONE 'NZT' FROM datetimetest_log;
      timezone
---------------------
 2016-04-11 09:51:35
 2016-04-11 09:51:37
(2 rows)


Having read the documentation (section 8.5.3), I can't actually see any
allowance for this formatted timezone (e.g. +12 or -12).  So, either the
functionality needs correcting and the documentation updated to reflect that
this is a valid timezone format or the functionality needs to be amended to
reject (i.e. error) this timezone format.

duncan.stokes@eyemagnet.com writes:
> For the following data:
> datetimetest=# SELECT * FROM datetimetest_log;
>  id |        datetime        | comment
> ----+------------------------+---------
>   1 | 2016-04-11 09:51:35+12 | Test #1
>   2 | 2016-04-11 09:51:37+12 | Test #2
> (2 rows)

> Get the following responses:
> datetimetest=# SELECT datetime AT TIME ZONE INTERVAL '+12:00' FROM
> datetimetest_log;
>       timezone
> ---------------------
>  2016-04-11 09:51:35
>  2016-04-11 09:51:37
> (2 rows)

> datetimetest=# SELECT datetime AT TIME ZONE '+12' FROM datetimetest_log;
>       timezone
> ---------------------
>  2016-04-10 09:51:35   ** WRONG DATE FOR +12 ZONE **
>  2016-04-10 09:51:37   ** WRONG DATE FOR +12 ZONE **
> (2 rows)

No, it isn't wrong.  A time zone specified by an INTERVAL constant
follows the ISO8601 convention that positive offsets are east of
Greenwich; and that's also the convention we use when displaying
timestamptz values, so your first two sets of results match up.
In time zones specified by name, we follow the POSIX convention
(also used by the IANA timezone folk) in which positive offsets are
west of Greenwich.  The great thing about standards is there are
so many to choose from :-(

> Having read the documentation (section 8.5.3), I can't actually see any
> allowance for this formatted timezone (e.g. +12 or -12).

It's a POSIX timezone specification with an empty zone abbreviation
and no DST part.

            regards, tom lane