Обсуждение: BUG #19053: Inconsistent arithmetic regarding TIMESTAMPTZ and INTERVAL

The following bug has been logged on the website:

Bug reference:      19053
Logged by:          Jan Behrens
Email address:      jbe-mlist@magnetkern.de
PostgreSQL version: 17.5
Operating system:   FreeBSD
Description:

Consider the following calculations:

jbe=# SET TIME ZONE 'Europe/Berlin';
SET
jbe=# SELECT TIMESTAMPTZ '2025-10-27' - TIMESTAMPTZ '2025-10-26';
    ?column?
----------------
 1 day 01:00:00
(1 row)

jbe=# SELECT TIMESTAMPTZ '2025-10-26' + INTERVAL '1 day 01:00:00'; -- the
following result is surprising, as it is not midnight
        ?column?
------------------------
 2025-10-27 01:00:00+01
(1 row)

jbe=# SELECT TIMESTAMPTZ '2025-03-31' - TIMESTAMPTZ '2025-03-30';
 ?column?
----------
 23:00:00
(1 row)

jbe=# SELECT TIMESTAMPTZ '2025-03-30' + INTERVAL '23:00:00'; -- inconsistent
with the previous addition above, as it is midnight
        ?column?
------------------------
 2025-03-31 00:00:00+02
(1 row)

jbe=# SELECT TIMESTAMPTZ '2025-04-01' - TIMESTAMPTZ '2025-03-30';
    ?column?
----------------
 1 day 23:00:00
(1 row)

jbe=# SELECT TIMESTAMPTZ '2025-03-30' + INTERVAL '1 day 23:00:00'; -- here,
the result isn't midnight again
        ?column?
------------------------
 2025-03-31 23:00:00+02
(1 row)

Or, some of these operations written in a single expression:

jbe=# SELECT TIMESTAMPTZ '2025-03-30' + (TIMESTAMPTZ '2025-03-31' -
TIMESTAMPTZ '2025-03-30');
        ?column?
------------------------
 2025-03-31 00:00:00+02
(1 row)

jbe=# SELECT TIMESTAMPTZ '2025-03-30' + (TIMESTAMPTZ '2025-04-01' -
TIMESTAMPTZ '2025-03-30');
        ?column?
------------------------
 2025-03-31 23:00:00+02
(1 row)

Note that there is no time zone change in between 2025-03-31 and 2025-04-01,
yet the time of the previous two calculations is off by one hour.

PG Bug reporting form <noreply@postgresql.org> writes:
> Consider the following calculations:

AFAICS all of these are behaving as-expected.  Yeah, it's confusing,
but expecting calendar calculations to have mathematical rigor is
a fool's errand.  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.

            regards, tom lane

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