Summing of INTERVAL returns incorrect number of days and hours
| От | Milan Novotny |
|---|---|
| Тема | Summing of INTERVAL returns incorrect number of days and hours |
| Дата | |
| Msg-id | CACZpPHoN2wHXDushnJqJy3Aus-MzYq_V3WjB9KaU8ATjXdrQeg@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: Summing of INTERVAL returns incorrect number of days and hours
|
| Список | pgsql-bugs |
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
В списке pgsql-bugs по дате отправления: