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

On Wed, Apr 26, 2017 at 8:23 PM, <pietro(dot)pugni(at)gmail(dot)com> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 14632
> Logged by: Pietro Pugni
> Email address: pietro(dot)pugni(at)gmail(dot)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'.

And if you change that, then this happens:

SELECT date '1912-02-28' + interval '10 years' - interval '10 years';
?column?
---------------------
1912-02-29 00:00:00
(1 row)

which is obviously not correct either.

The problem is that after the subtraction the date '1912-02-28' doesn't
know it's supposed to be the last date of the month instead of the 28th,
specifically. And you can't really fix that without breaking pg_upgrade
and probably hundreds of applications using the binary format for dates.

You'll just have to take this into account when working on your application.

.m