Thread: [BUGS] BUG #14632: Plus and minus operators inconsistency with leap yearsand year intervals.

Il giorno 26 apr 2017, alle ore 20:29, David G. Johnston <david.g.johnston@gmail.com> ha scritto:

On Wed, Apr 26, 2017 at 11:23 AM, <pietro.pugni@gmail.com> wrote:

The following bug has been logged on the website:

Bug reference:      14632
Logged by:          Pietro Pugni
Email address:      pietro.pugni@gmail.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.​

Pietro Pugni <pietro.pugni@gmail.com> writes:

Adding 10 years to 1912-02-29 returns 1922-02-29, as expected.

No, it doesn't, and it would be wrong to do so because there is no
such date; 1922 wasn't a leap year.

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.

As remarked somewhere in our documentation, you'd need to take that up
with the Pope, not with us database hackers.  We didn't invent the
calendar rules.

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

On Wed, Apr 26, 2017 at 3:44 PM, Pietro Pugni <pietro.pugni@gmail.com> wrote:

I don't know if you intended to supersize your response but it ended up that way in my mail client.

I'll agree that the age function has enough data available to it to return 10 years in this case.

28 days, target month is February in a non-leap-year (1922), convert to 1 month.  11 months + 1 month = 12 months = 1 year.  9 years + 1 year = 10 years.

But the provided answer is correct as well...

On Thu, Apr 27, 2017 at 1:01 AM, Pietro Pugni <pietro.pugni@gmail.com> wrote:

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.

And this is logically wrong because it leads to wrong results. I’m aware that time intervals are difficult to manage but more exactness is needed here: '10 years' must have the same meaning when added to a date and subtracted from it, otherwise it leads to wrong results. 

Your suggestion just moves the wrong results to another use case; see my response upthread.  There is no objectively correct answer here, like you seem to think.

Il giorno 27 apr 2017, alle ore 21:55, Pedro Gimeno <pgsql-004@personal.formauri.es> ha scritto:

I would like to better understand your expectations regarding your proposed fix.

1. What do you propose that this specific query should return?

SELECT '1912-02-28'::date + interval '10 years’;

2. What do you propose that this specific query should return?

SELECT (whatever your answer to question 1 is)::date - interval '10 years’;

3. What do you propose that this specific query should return?

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

4. What do you propose that this specific query should return?

SELECT (whatever your answer to question 3 is)::date - interval '10 years’;

5. What do you propose that this specific query should return?

SELECT '1912-03-01'::date + interval '10 years’;

6. What do you propose that this specific query should return?

SELECT (whatever your answer to question 5 is)::date - interval '10 years’;

Il giorno 27 apr 2017, alle ore 14:31, Pantelis Theodosiou <ypercube@gmail.com> ha scritto:

You seem to think that expressions similar to these should yield the same results:

    dateD + intervalA + intervalB

    (dateD + intervalA) + intervalB

    dateD + (intervalA + intervalB)

But they don't and they couldn't, as many others have mentioned already.

Expressions 1 and 2 are equivalent but the 3rd is not.

And we don't even need leap years to find such "wrong" results. We have leap seconds (which are rare) and leap months (which are pretty common although we don't call them leap months). Just try 

SELECT 
   '2017-03-31'::date + '1 month'::interval + '1 month'::interval,
   '2017-03-31'::date + ('1 month'::interval + '1 month'::interval) ;

Not all months have the same number of days and there is not a way to fix that.

It's just not possible to squeeze 31 days in a 30-days month. What should  '2017-03-31'::date + '1 month'::interval result and what should '2017-03-30'::date + '1 month'::interval result? 

Regards, 
Pantelis

Probably it should be worth it to implement a sort of age2(timestamp, timestamp) function that returns ages accounting for leap years, months, days and seconds, without changing the actual implementation of age(timestamp, timestamp).