Re: Issue with interval calculation when adding 1 year to a non-leap year

On Fri, 2024-03-08 at 07:57 +0000, PG Doc comments form wrote:
> I have encountered a potential issue with the interval calculation when
> adding 1 year to a non-leap year. The behavior observed is that when adding
> 1 year to a date in a non-leap year such as 2023, the resulting date is
> incorrectly set to February 28th instead of February 29th in the following
> leap year, which should be 2024.
>
> This behavior is inconsistent with the expected behavior, as it does not
> account for leap years when performing date arithmetic using intervals.
>
> Please find below a sample query illustrating the issue:
> SELECT ('2023-02-28 23:59:59')::timestamp + INTERVAL '1 year';
>
> Expected result: '2024-02-29 23:59:59'
> Actual result: '2024-02-28 23:59:59'

If somebody tells me to meet again on the same day one year from
now on Feb 28, I would show up on Feb 28 the next year.

I understand that you are thinking of "the last day of the month",
but with that reasoning you could say that

2023-02-27 00:00:00 + 1 year = 2024-02-28 00:00:00

or indeed

2023-02-01 00:00:00 + 1 year = 2024-02-02 00:00:00

Somewhat in favor of your interpretation is

SELECT '2024-02-29 12:00:00'::timestamp - '1 year'::interval;

?column?
═════════════════════
2023-02-28 12:00:00
(1 row)

So we have

2024-02-29 12:00:00 - 1 year + 1 year != 2024-02-29 12:00:00

and indeed

2024-02-29 12:00:00 - 1 year = 2024-02-28 12:00:00 - 1 year

I'd say that there is simply no way to make all this consistent,
and the current implementation is what I would intuitively expect.

Yours,
Laurenz Albe