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

On Wed, Apr 26, 2017 at 1:30 PM, Pietro Pugni <pietro(dot)pugni(at)gmail(dot)com>
wrote:

> 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*.
>
>
​In theory we could simply die trying but given few complaints this
behavior doesn't seem to bother many people if not actually please them
because at least they can get a correct result even if it is munged a bit.
Date interval math is fraught with problems.; you can bypass most of them
by manipulating days instead.

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'.
>

Expect 1912-02-28 is a correct response. The only reason you think the
29th comes into play here is because you remember that the starting point
was the 29th. The system has no such memory.​

It’s an issue because there’s no other way to obtain the original date
> (reversing the add operation).
>

You are correct. Given the presence of leap years what you describe is a
mathematical impossibility - not a bug.

>
>
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.
>
>
​It resides in the fact we apply date shifting after applying the interval
in order to come up with a valid date. That such date shifting negates the
commutative property is an unfortunate byproduct. To my knowledge there is
no promise nor requirement for date arithmetic to be commutative. Or, more
precisely, if you wish to use the commutative property here you must
operate using days.

>
> 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)!
>

​Operate in days and you can do this just fine - but note you cannot just
convert the interval to a number of days.

​SELECT '1922-02-28'::date - '1912-02-29'::date ; 3652
SELECT '1922-02-28'::date - 3652 ; 1912-02-29

This particular example leads me to suspect that some improvement in this
area might be possible...

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.
>

How?