Re: Incorrect handling of timezones with extract

Michael Paquier <michael(dot)paquier(at)gmail(dot)com> writes:
> On Tue, Mar 12, 2013 at 3:11 PM, Michael Paquier
> <michael(dot)paquier(at)gmail(dot)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