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"
|
Список | 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 по дате отправления: