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

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.

Responses

Browse pgsql-bugs by date

  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