pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedIssue with interval calculation when adding 1 year to a non-leap year
2+ messages / 2 participants
[nested] [flat]
* Issue with interval calculation when adding 1 year to a non-leap year
@ 2024-03-08 07:57 PG Doc comments form <noreply@postgresql.org>
0 siblings, 1 reply; 2+ messages in thread
From: PG Doc comments form @ 2024-03-08 07:57 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: mdriyazathcse@gmail.com
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Issue with interval calculation when adding 1 year to a non-leap year
@ 2024-03-08 12:14 Laurenz Albe <laurenz.albe@cybertec.at>
parent: PG Doc comments form <noreply@postgresql.org>
0 siblings, 0 replies; 2+ messages in thread
From: Laurenz Albe @ 2024-03-08 12:14 UTC (permalink / raw)
To: mdriyazathcse@gmail.com; pgsql-docs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-03-08 12:14 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-03-08 07:57 Issue with interval calculation when adding 1 year to a non-leap year PG Doc comments form <noreply@postgresql.org>
2024-03-08 12:14 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox