Thread: Can we consider "24 Hours" for "next day" in INTERVAL datatype ?

On Tue, 2022-03-15 at 12:54 +0530, Prabhat Sahu wrote:
> Kindly check the below scenario with INTERVAL datatype.
> 
> postgres=# select interval '01 20:59:59' + interval '00 05:00:01' as interval;
>     interval    
> ----------------
>  1 day 26:00:00
> (1 row)
> 
> Any operation with INTERVAL data, We are changing the interval values as 
> "60 sec" as "next minute"
> "60 min" as "next hour"
> Similarly can't we consider "24 Hours" for "next day" ?
> Is there any specific purpose we are holding the hours as an increasing number beyond 24 hours also?
> 
> But when we are dealing with TIMESTAMP with INTERVAL values it's considered the "24 Hours" for "next day".
> 
> postgres=# select timestamp '01-MAR-22 20:59:59' + interval '00 05:00:01'  as interval;
>       interval       
> ---------------------
>  2022-03-02 02:00:00
> (1 row)

The case is different with days:

test=> SELECT TIMESTAMPTZ '2022-03-26 20:00:00 Europe/Vienna' + INTERVAL '12 hours' + INTERVAL '12 hours';
        ?column?        
════════════════════════
 2022-03-27 21:00:00+02
(1 row)

test=> SELECT TIMESTAMPTZ '2022-03-26 20:00:00 Europe/Vienna' + INTERVAL '1 day';
        ?column?        
════════════════════════
 2022-03-27 20:00:00+02
(1 row)

Yours,
Laurenz Albe

Hi,

On Tue, Mar 15, 2022 at 12:54:58PM +0530, Prabhat Sahu wrote:
>
> Kindly check the below scenario with INTERVAL datatype.
> 
> postgres=# select interval '01 20:59:59' + interval '00 05:00:01' as
> interval;
>     interval
> ----------------
>  1 day 26:00:00
> (1 row)
> 
> Any operation with INTERVAL data, We are changing the interval values as
> "60 sec" as "next minute"
> "60 min" as "next hour"
> *Similarly can't we consider "24 Hours" for "next day" ?*
> Is there any specific purpose we are holding the hours as an increasing
> number beyond 24 hours also?

Yes, you can't blindly assume that adding 24 hours will always be the same as
adding a day.  You can just justify_days if you want to force that behavior.

On Tue, Mar 15, 2022 at 3:46 AM Julien Rouhaud <rjuju123@gmail.com> wrote:
> On Tue, Mar 15, 2022 at 12:54:58PM +0530, Prabhat Sahu wrote:
> >
> > Is there any specific purpose we are holding the hours as an increasing
> > number beyond 24 hours also?
>
> Yes, you can't blindly assume that adding 24 hours will always be the same as
> adding a day.  You can just justify_days if you want to force that behavior.

The specific purpose by the way, at least according to the docs [1],
is daylights savings time:
> Internally interval values are stored as months, days, and microseconds. This is done because
> the number of days in a month varies, and a day can have 23 or 25 hours if a daylight savings
> time adjustment is involved.
Though I suppose leap seconds may also follow similar logic.

[1] https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-INTERVAL-INPUT

- Joe Koshakow