Incorrect handling of timezones with extract

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

Responses

Browse pgsql-hackers by date

  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]