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
