Michael Paquier <michael.paqu...@gmail.com> writes: > On Tue, Mar 12, 2013 at 3:11 PM, Michael Paquier > <michael.paqu...@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.1021949...@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
