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

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/16/functions-datetime.html
Description:

Dear PostgreSQL Developers,

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'

This issue could potentially impact applications relying on accurate date
arithmetic, particularly in scenarios involving financial calculations or
date projections.

Thank you for your attention to this matter.

Sincerely,
Mohamed Riyazath
mdriyazathcse@gmail.com

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