Summing of INTERVAL returns incorrect number of days and hours
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
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