| From: | Milan Novotny <xnovm149(at)gmail(dot)com> |
|---|---|
| To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
| Subject: | Summing of INTERVAL returns incorrect number of days and hours |
| Date: | 2025-04-01 13:13:08 |
| Message-ID: | CACZpPHoN2wHXDushnJqJy3Aus-MzYq_V3WjB9KaU8ATjXdrQeg@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2025-04-01 14:19:36 | Re: Summing of INTERVAL returns incorrect number of days and hours |
| Previous Message | Christoph Berg | 2025-04-01 10:46:02 | Re: Self referential foreign keys in partitioned table not working as expected |