Thread

1. BUG #19053: Inconsistent arithmetic regarding TIMESTAMPTZ and INTERVAL

   PG Bug reporting form <noreply@postgresql.org> — 2025-09-15T10:12:58Z

   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.
   
   

2. Re: BUG #19053: Inconsistent arithmetic regarding TIMESTAMPTZ and INTERVAL

   Tom Lane <tgl@sss.pgh.pa.us> — 2025-09-15T15:41:29Z

   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
   
   
   
   

3. Re:BUG #19053: Inconsistent arithmetic regarding TIMESTAMPTZ and INTERVAL

   ocean_li_996 <ocean_li_996@163.com> — 2025-09-15T16:03:42Z

   Hello,
   I think this is caused by the daylight saving time and standard time changes in the Europe/Berlin timezone. 
   It is not a bug, but the expected behavior. In 2025, daylight saving time starts at '2025-03-30 01:00 UTC' 
   and ends at '2025-10-26 01:00 UTC'. When changing, it will make one hour gap. 
   
   
   --
   
   regards
   Haiyang Li
   
   

4. Re: BUG #19053: Inconsistent arithmetic regarding TIMESTAMPTZ and INTERVAL

   Jan Behrens <jbe-mlist@magnetkern.de> — 2025-09-15T17:24:17Z

   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