Incorrect handling of timezones with extract
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
On Tue, Mar 12, 2013 at 3:11 PM, Michael Paquier
<michael.paquier@gmail.com>wrote:
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:
Also note that this happens only with the timezone set where time -1day.
Sorry for the noise, I found the same question answered here:
/messages/by-id/17307.1021949260@sss.pgh.pa.us
This is due to the fact that US/Pacific moved to DST last weekend.
--
Michael
Michael Paquier <michael.paquier@gmail.com> writes:
On Tue, Mar 12, 2013 at 3:11 PM, Michael Paquier
<michael.paquier@gmail.com>wrote:
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.
Sorry for the noise, I found the same question answered here:
/messages/by-id/17307.1021949260@sss.pgh.pa.us
Well, the answer was different in 2002 ;-). Back then, interval
subtraction worked like this:
play=> select now();
now
------------------------
2013-03-12 13:02:23-04
(1 row)
play=> select now() + '-3 days'::interval;
?column?
------------------------
2013-03-09 12:02:26-05
(1 row)
(tested on a 7.0 postmaster). In modern PG versions it works like this:
regression=# select now();
now
-------------------------------
2013-03-12 13:02:45.961634-04
(1 row)
regression=# select now() + '-3 days'::interval;
?column?
-------------------------------
2013-03-09 13:02:47.833714-05
(1 row)
Note the nominal hour remains the same across the DST transition. So you get
regression=# select (now() + '-3 days'::interval) - now();
?column?
-------------------
-2 days -23:00:00
(1 row)
and extract(day) from that gives -2 not -3. You could argue that this
definition of timestamp subtraction isn't too consistent with the
timestamp-plus-interval operator, and you'd be right; but I doubt we'd
consider changing it now.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
and extract(day) from that gives -2 not -3. You could argue that this
definition of timestamp subtraction isn't too consistent with the
timestamp-plus-interval operator, and you'd be right; but I doubt we'd
consider changing it now.
We specifically added that feature to support production calendaring
applications; I worked on it with Karel. When someone calendars a event
to be "3 days later" they don't expect it to jump by an hour because it
crossed a DST boundary. So changing it would break a bunch of people's
apps, especially mine.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Josh Berkus <josh@agliodbs.com> writes:
and extract(day) from that gives -2 not -3. You could argue that this
definition of timestamp subtraction isn't too consistent with the
timestamp-plus-interval operator, and you'd be right; but I doubt we'd
consider changing it now.
We specifically added that feature to support production calendaring
applications; I worked on it with Karel. When someone calendars a event
to be "3 days later" they don't expect it to jump by an hour because it
crossed a DST boundary. So changing it would break a bunch of people's
apps, especially mine.
The behavior of timestamp-plus-interval is certainly supported by that
argument, but I'm less convinced about timestamp-minus-timestamp. The
raw result of the timestamp subtraction here is 71 hours (not the normal
72). Perhaps it should be outputting it that way instead of converting
to "2 days 23 hours", which is arguably inaccurate.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
The behavior of timestamp-plus-interval is certainly supported by that
argument, but I'm less convinced about timestamp-minus-timestamp. The
raw result of the timestamp subtraction here is 71 hours (not the normal
72). Perhaps it should be outputting it that way instead of converting
to "2 days 23 hours", which is arguably inaccurate.
Probably, yes. We added a barrier so that interval is supposed to be
years-months | weeks-days | hours-minutes-seconds-ms-ns. However, it
sounds like we missed a few cases.
Mind you, there's a kind of insurmountable issue with timestamp
subtraction, where you can come up with *lots* of cases where timestamp
subtraction is not reversable due to the way we handle intervalization.
Realistically, we'd need to have something like a ts_sub( tstz, tstz,
interval ) function to have a 100% reversible option. That probably
contributes to why Oracle has never supported timestamp - timestamp.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers