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

> Il giorno 27 apr 2017, alle ore 21:55, Pedro Gimeno <pgsql-004(at)personal(dot)formauri(dot)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’;

1922-02-28
(same result returned by Postgres)

> 2. What do you propose that this specific query should return?
>
> SELECT (whatever your answer to question 1 is)::date - interval '10 years’;

1912-02-28
(same result returned by Postgres but collides with result returned at point 4.)

> 3. What do you propose that this specific query should return?
>
> SELECT '1912-02-29'::date + interval '10 years’;

1922-02-28
(same result returned by Postgres)

> 4. What do you propose that this specific query should return?
>
> SELECT (whatever your answer to question 3 is)::date - interval '10 years’;

1912-02-29
(different from result returned at point 2.)

> 5. What do you propose that this specific query should return?
>
> SELECT '1912-03-01'::date + interval '10 years’;

1922-03-01
(same result returned by Postgres)

> 6. What do you propose that this specific query should return?
>
> SELECT (whatever your answer to question 5 is)::date - interval '10 years’;

1912-03-01
(same result returned by Postgres)

I’m aware that 2. and 4. can’t return different result because they are the same queries, but in that cases the result correctness depends on the context.

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

Kind regards,
Pietro Pugni