Can we consider "24 Hours" for "next day" in INTERVAL datatype ?
Hi All, 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) -- With Regards, Prabhat Kumar Sahu EnterpriseDB: http://www.enterprisedb.com
Re: 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
Re: Can we consider "24 Hours" for "next day" in INTERVAL datatype ?
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.
Re: Can we consider "24 Hours" for "next day" in INTERVAL datatype ?
On Tue, Mar 15, 2022 at 3:46 AM Julien Rouhaud 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
