Re: BUG #14632: Plus and minus operators inconsistency with leap years and year intervals.

I’ll try to reformulate better.

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.

The “logical” bug can be seen also by adding and subtracting the same quantity:

SELECT '1912-02-29'::date - '10 years'::interval + '10 years'::interval

It returns '1912-02-28 00:00:00' instead of '1912-02-29 00:00:00'.

It’s an issue because there’s no other way to obtain the original date (reversing the add operation). The function age(timestamp, timestamp), for example, behave exactly as adding and subtracting the interval:
SELECT age('1922-02-28'::date, '1912-02-29'::date)
returns:
9 years 11 mons 28 days
which is inconsistent against the result returned by adding 10 years to 1912-02-29:
SELECT '1912-02-29'::date + '10 years'::interval
returns:
(a) '1922-02-28 00:00:00'
while
SELECT '1922-02-28'::date - '9 years 11 mons 28 days'::interval
returns:
(b) '1912-02-29 00:00:00'
Please, note the difference of 1 day between (a) and (b).

Also:
SELECT age('1922-02-28'::date, '1912-02-29'::date) = '10 years'::interval
returns false, while:
SELECT ('1922-02-29'::date + '10 years'::interval) = '1922-02-28'::date
returns true.

The inconsistency (or bug) resides in the non unique meaning of the interval handling with respect of summing and subtracting the same quantity.

Again, the issue can be seen this way: adding the interval returned by SELECT age('1922-02-28'::date, '1912-02-29'::date) to the original date. So:
SELECT '1912-02-29'::date + '9 years 11 mons 28 days'::interval
returns:
(c) 1922-02-26 00:00:00
Here we loose 2 days even if we add the same interval used in (b)!

The bug basically consists of the vague meaning of “years” applied to leap years. It should be revised in order to be consistent and correct.

Kind regards,
Pietro Pugni

> Il giorno 26 apr 2017, alle ore 20:29, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> ha scritto:
>
> On Wed, Apr 26, 2017 at 11:23 AM, <pietro(dot)pugni(at)gmail(dot)com <mailto:pietro(dot)pugni(at)gmail(dot)com>> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 14632
> Logged by: Pietro Pugni
> Email address: pietro(dot)pugni(at)gmail(dot)com <mailto:pietro(dot)pugni(at)gmail(dot)com>
> PostgreSQL version: 9.6.2
> Operating system: Ubuntu 16.04.2 LTS
> Description:
>
> The following query:
> SELECT '1912-02-29'::date + '10 years'::interval
>
> returns:
> '1922-02-28 00:00:00'
>
> while the reverse operation:
> SELECT '1922-02-28'::date - '10 years'::interval
>
> returns:
> '1912-02-28 00:00:00'
>
> instead of '1912-02-29 00:00:00'.
>
> ​What part of this do you consider to be a bug - and what should it do instead?
>
> David J.​