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.
