Re: Incorrect handling of timezones with extract

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