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