Re: [BUGS] BUG #14632: Plus and minus operators inconsistency withleap years and year intervals. - Mailing list pgsql-bugs

From John R Pierce
Subject Re: [BUGS] BUG #14632: Plus and minus operators inconsistency withleap years and year intervals.
Date
Msg-id eae3875c-9c21-faf7-9adb-6ab07465e553@hogranch.com
Whole thread Raw
In response to Re: [BUGS] BUG #14632: Plus and minus operators inconsistency withleap years and year intervals.  (John R Pierce <pierce@hogranch.com>)
List pgsql-bugs
On 4/26/2017 1:53 PM, John R Pierce wrote:
On 4/26/2017 1:30 PM, Pietro Pugni wrote:
Adding 10 years to 1912-02-29 returns 1922-02-29, as expected.
I would like to apply the reverse operation. To do so, I subtract 10 years from 1922-02-29 but I obtain 1912-02-28, so the math is actually wrong.


assuming 1922 was a leap year, 1912 is NOT a leap year, so therefore there is no 1912-02-29, that is an invalid date.

ok, I got that backwards. 1912 is the leap year.

date arithmetic is not guaranteed to be associative or commutative due to the irregular units involved.

pierce=# SELECT date '1912-02-29';
    date   
------------
 1912-02-29
(1 row)

pierce=# SELECT date '1922-02-29';
ERROR:  date/time field value out of range: "1922-02-29"
LINE 1: SELECT date '1922-02-29';
               ^
pierce=# SELECT date '1912-02-29' - interval '10 years';
      ?column?      
---------------------
 1902-02-28 00:00:00
(1 row)

pierce=# SELECT date '1912-02-29' + interval '10 years';
      ?column?      
---------------------
 1922-02-28 00:00:00
(1 row)

as an even more extreme case...

pierce=# SELECT date '2017-04-30' - interval '2 months';
      ?column?      
---------------------
 2017-02-28 00:00:00
(1 row)



-- 
john r pierce, recycling bits in santa cruz

pgsql-bugs by date:

Previous
From: Marko Tiikkaja
Date:
Subject: Re: [BUGS] BUG #14632: Plus and minus operators inconsistency withleap years and year intervals.
Next
From: Marko Tiikkaja
Date:
Subject: Re: [BUGS] BUG #14632: Plus and minus operators inconsistency withleap years and year intervals.