From: Laurenz Albe <laurenz.albe@cybertec.at>
To: mdriyazathcse@gmail.com
To: pgsql-docs@lists.postgresql.org
Subject: Re: Issue with interval calculation when adding 1 year to a non-leap year
Date: Fri, 08 Mar 2024 13:14:29 +0100
Message-ID: <fa35ba4e725585c7434e5433c3929153e0da6763.camel@cybertec.at> (raw)
In-Reply-To: <170988464798.644.7130288736118645836@wrigleys.postgresql.org>
References: <170988464798.644.7130288736118645836@wrigleys.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: laurenz.albe@cybertec.at, mdriyazathcse@gmail.com, pgsql-docs@lists.postgresql.org
Subject: Re: Issue with interval calculation when adding 1 year to a non-leap year
In-Reply-To: <fa35ba4e725585c7434e5433c3929153e0da6763.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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