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