From: | Michael Paquier <michael(dot)paquier(at)gmail(dot)com> |
---|---|
To: | PostgreSQL mailing lists <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Incorrect handling of timezones with extract |
Date: | 2013-03-12 06:11:49 |
Message-ID: | CAB7nPqSttqhe4t-YEGCAT+yp0i7Sm8twAOacw9j_G7vdgyTpVQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Etsuro Fujita | 2013-03-12 06:35:22 | Fix document typo |
Previous Message | Amit Kapila | 2013-03-12 05:16:53 | Re: Proposal for Allow postgresql.conf values to be changed via SQL [review] |