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

​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?