Hi all,
When running some QE tests at VMware, we found an error with extract
handling timezones.
Please see below:
postgres=# show timezone;
TimeZone
------------
Asia/Tokyo
(1 row)
postgres=# select now();
now
-------------------------------
2013-03-12 14:54:28.911298+09
(1 row)
postgres=# select extract(day from ((CAST(-3 || 'day' as interval)+now()) -
now()));
date_part
-----------
-3
(1 row)
postgres=# set timezone = 'US/Pacific';
SET
postgres=# select now();
now
-------------------------------
2013-03-11 22:56:10.317431-07
(1 row)
postgres=# select extract(day from ((CAST(-3 || 'day' as interval)+now()) -
now()));
date_part
-----------
-2
(1 row)
Here I believe that the correct result should be -3.
Note that it passes with values upper than -2 and lower than -127:
postgres=# select extract(day from ((CAST(-128 || 'day' as interval)+now())
- now()));
date_part
-----------
-128
(1 row)
postgres=# select extract(day from ((CAST(-127 || 'day' as interval)+now())
- now()));
date_part
-----------
-126
(1 row)
postgres=# select extract(day from ((CAST(-2 || 'day' as interval)+now()) -
now()));
date_part
-----------
-1
(1 row)
postgres=# select extract(day from ((CAST(-1 || 'day' as interval)+now()) -
now()));
date_part
-----------
-1
(1 row)
Also note that this happens only with the timezone set where time -1day.
postgres=# set timezone to 'Asia/Tokyo';
SET
postgres=# select extract(day from ((CAST(-127 || 'day' as interval)+now())
- now()));
date_part
-----------
-127
(1 row)
postgres=# select extract(day from ((CAST(-100 || 'day' as interval)+now())
- now()));
date_part
-----------
-100
(1 row)
postgres=# select extract(day from ((CAST(-2 || 'day' as interval)+now()) -
now()));
date_part
-----------
-2
(1 row)
I also tested with PG on master until 8.4 and could reproduce the problem.
Regards,
--
Michael
