From: Jan Behrens <jbe-mlist@magnetkern.de>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19053: Inconsistent arithmetic regarding TIMESTAMPTZ and INTERVAL
Date: Mon, 15 Sep 2025 19:24:17 +0200
Message-ID: <20250915192417.ba5eae9cdd76a1be244bd4fd@magnetkern.de> (raw)
In-Reply-To: <615313.1757950889@sss.pgh.pa.us>
References: <19053-669df0ccf3d14d52@postgresql.org>
<615313.1757950889@sss.pgh.pa.us>
On Mon, 15 Sep 2025 11:41:29 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> AFAICS all of these are behaving as-expected.
Well, I would say each calculation by itself can be somewhat explained.
But it's the overall combination of behavior that seems inconsistent.
In particular:
TIMESTAMPTZ '2025-10-27' - TIMESTAMPTZ '2025-10-26'
This gives INTERVAL '1 day 01:00:00', which makes sense in some way.
However, knowing that '24 hours' are not the same as '1 day', I would
rather expect either '25 hours' or '1 day'.
The following does actually make sense:
TIMESTAMPTZ '2025-10-26' + INTERVAL '1 day 01:00:00'
But it's the previous substraction that, given how INTERVAL behaves
here, does not make sense.
> Yeah, it's confusing,
> but expecting calendar calculations to have mathematical rigor is
> a fool's errand.
I don't expect mathematical rigor, but I would like to have consistent
semantics of what INTERVAL '1 day' means, and when it is used and when
it is returned.
> The intent of what's implemented is to produce
> useful results for calculations like
>
> regression=# SELECT TIMESTAMPTZ '2025-03-30' + INTERVAL '1 day';
> ?column?
> ------------------------
> 2025-03-31 00:00:00+02
> (1 row)
>
> despite the intervening DST change.
Yes, I agree that the addition behaves correctly. I just wonder if the
substraction would need to return either '1 day' or '25 hours'. But not
'1 day 01:00:00'.
> regards, tom lane
Regards,
Jan Behrens
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-bugs@postgresql.org
Cc: jbe-mlist@magnetkern.de, tgl@sss.pgh.pa.us, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19053: Inconsistent arithmetic regarding TIMESTAMPTZ and INTERVAL
In-Reply-To: <20250915192417.ba5eae9cdd76a1be244bd4fd@magnetkern.de>
* 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