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