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

From: PG Doc comments form <noreply(at)postgresql(dot)org>
To: pgsql-docs(at)lists(dot)postgresql(dot)org
Cc: mdriyazathcse(at)gmail(dot)com
Subject: Issue with interval calculation when adding 1 year to a non-leap year
Date: 2024-03-08 07:57:27
Message-ID: 170988464798.644.7130288736118645836@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs

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(at)gmail(dot)com

Responses

Browse pgsql-docs by date

  From Date Subject
Next Message Laurenz Albe 2024-03-08 12:14:29 Re: Issue with interval calculation when adding 1 year to a non-leap year
Previous Message PG Doc comments form 2024-03-07 09:56:58 Clarify pg_rewind behaviour in case of no divergence