The following bug has been logged on the website:
Bug reference: 8572
Logged by: Combination of SET TIME ZONEs and CAST gives wrong results
Email address: przemek@hadapt.com
PostgreSQL version: 9.1.10
Operating system: Ubuntu 12.04.3 LTS
Description:
Reproduction:
postgres=# set time zone 'Europe/Warsaw';
SET
postgres=# set time zone '-1.5';
SET
postgres=# select cast('2012-12-12 12:00 Europe/Warsaw' AS timestamp with
time zone);
timestamptz
---------------------------
2012-12-12 12:00:00-01:30
(1 row)
The same happens for other timezones too, like:
postgres=# set time zone 'Europe/London';
SET
postgres=# set time zone '-1.5';
SET
postgres=# select cast('2012-12-12 12:00 Europe/London' AS timestamp with
time zone);
timestamptz
---------------------------
2012-12-12 12:00:00-01:30
(1 row)
Note, however, that it happens only if the first SET value matches the
timezone given in timestamp string. If they differ, then the result is
correct:
postgres=# set time zone 'Europe/London';
SET
postgres=# set time zone '-1.5';
SET
postgres=# select cast('2012-12-12 12:00 Europe/Warsaw' AS timestamp with
time zone);
timestamptz
---------------------------
2012-12-12 09:30:00-01:30
(1 row)
Bug exsists in 9.2.2 (tested on CentOS 6.3), 9.1.10 (Ubuntu 12.04.3 LTS) and
9.0.6 (CentOS 6.3) versions
