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

On Wed, Apr 26, 2017 at 4:01 PM, Pietro Pugni <pietro(dot)pugni(at)gmail(dot)com>
wrote:

> ​​
>
> 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.
>
>
> It should be advised in the official docs (I can’t find any reference for
> this topic).
>
>
There are 365 days in a normal year and 366 days in a leap year. There
does not exist a function that can completely map from the normal domain to
the leap year domain (I'm stretching my math limits here, forgive or
correct minor inaccuracies). The 28th day of a non-leap year must either
be the 28th day of a leap year or the 29th day of a leap year, it cannot be
both (by definition of a function). Since the definition of "+/- 1 year"
is to simply increment/decrement the year by 1 we have chosen the first
outcome. There is a map the other direction though, because the 29th can
and is mapped to the 28th.

I haven't and am not presently slogging through docs to further support or
refute how well this is documented. If you have suggestions, or better, a
patch, they are welcome.

>
> How?
>
>
> Probably considering leap dates as singularities and threat them by
> subtracting 1 day.
> For example, a possible workaround to properly calculate the interval
> between 1922-02-28 and 1922-02-29 is the following:
>
> SELECT age('1922-02-28'::date, '1912-02-29'::date - '1 day'::interval)
> age
> ----------
> 10 years
>

At first glance I would consider this a worthwhile path to explore; and it
meshes well with the comment I made above (before reading this) about the
functions are either ignoring or mapping the 29th to the 28th. Subtracting
1 day to leap-year dates effective does both. I'm sure there are other
interactions involved here but if for someone willing to do the work this
approach seems to have some merit.

David J.