BUG #14082: Unexpected time adjustment for output using "at time zone"

Поиск
Список
Период
Сортировка
От duncan.stokes@eyemagnet.com
Тема BUG #14082: Unexpected time adjustment for output using "at time zone"
Дата
Msg-id 20160410220738.15199.93161@wrigleys.postgresql.org
обсуждение исходный текст
Ответы Re: BUG #14082: Unexpected time adjustment for output using "at time zone"  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
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.

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Andres Freund
Дата:
Сообщение: Re: [HACKERS] Breakage with VACUUM ANALYSE + partitions
Следующее
От: Tom Lane
Дата:
Сообщение: Re: BUG #14082: Unexpected time adjustment for output using "at time zone"