Thread: Incorrect handling of timezones with extract

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

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:
> http://www.postgresql.org/message-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

> 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

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

> 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
ats_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