From: | duncan(dot)stokes(at)eyemagnet(dot)com |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #14082: Unexpected time adjustment for output using "at time zone" |
Date: | 2016-04-10 22:07:38 |
Message-ID: | 20160410220738.15199.93161@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 14082
Logged by: Duncan Stokes
Email address: duncan(dot)stokes(at)eyemagnet(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Andres Freund | 2016-04-11 16:50:54 | Re: [BUGS] Breakage with VACUUM ANALYSE + partitions |
Previous Message | Noah Misch | 2016-04-10 20:55:21 | Re: BUG #14028: FATAL: cannot perform encoding conversion outside a transaction |