Thread

Summing of INTERVAL returns incorrect number of days and hours

Milan Novotny <xnovm149@gmail.com> — 2025-04-01T13:13:08Z

Hello,

SELECT

SUM(upper(timestamp_range) - lower(timestamp_range)) AS sum_range_interval

FROM

(

VALUES

('["2021-09-13 10:53:03.668","2022-08-28 00:19:16.212"]'::tsrange),

('["2022-08-29 00:00:03.088","2023-04-06 11:39:00.072"]'::tsrange),

('["2023-04-06 11:39:03.581","2023-04-19 23:59:59.768"]'::tsrange),

('["2022-08-28 00:19:28.739","2022-08-28 23:59:59.829"]'::tsrange)

) AS x(timestamp_range);

/*

sum_range_interval |

---------------------+

581 days 61:06:36.805|

*/

--> should be 583 days ..


SELECT

upper(timestamp_range) - lower(timestamp_range) AS range_interval

FROM

(

VALUES

('["2021-09-13 10:53:03.668","2022-08-28 00:19:16.212"]'::tsrange),

('["2022-08-29 00:00:03.088","2023-04-06 11:39:00.072"]'::tsrange),

('["2023-04-06 11:39:03.581","2023-04-19 23:59:59.768"]'::tsrange),

('["2022-08-28 00:19:28.739","2022-08-28 23:59:59.829"]'::tsrange)

) AS x(timestamp_range);

/*

range_interval |

---------------------+

348 days 13:26:12.544|

220 days 11:38:56.984|

13 days 12:20:56.187|

23:40:31.09|

*/


SELECT

SUM(range_interval) AS sum_range_interval

FROM

(

VALUES

('348 days 13:26:12.544'::INTERVAL),

('220 days 11:38:56.984'::INTERVAL),

('13 days 12:20:56.187'::INTERVAL),

('23:40:31.09'::INTERVAL)

) AS x(range_interval);

/*

sum_range_interval |

---------------------+

581 days 61:06:36.805|

*/

--> same

Re: Summing of INTERVAL returns incorrect number of days and hours

Tom Lane <tgl@sss.pgh.pa.us> — 2025-04-01T14:19:36Z

Milan Novotny <xnovm149@gmail.com> writes:
> 581 days 61:06:36.805|
> --> should be 583 days ..

If you don't like that result, justify_hours() might help you.
But interval summing doesn't assume that it can always overflow
seconds into days, because a day isn't always 24 hours.

			regards, tom lane