pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedSumming of INTERVAL returns incorrect number of days and hours
2+ messages / 2 participants
[nested] [flat]
* Summing of INTERVAL returns incorrect number of days and hours
@ 2025-04-01 13:13 Milan Novotny <xnovm149@gmail.com>
2025-04-01 14:19 ` Re: Summing of INTERVAL returns incorrect number of days and hours Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Milan Novotny @ 2025-04-01 13:13 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Summing of INTERVAL returns incorrect number of days and hours
2025-04-01 13:13 Summing of INTERVAL returns incorrect number of days and hours Milan Novotny <xnovm149@gmail.com>
@ 2025-04-01 14:19 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2025-04-01 14:19 UTC (permalink / raw)
To: Milan Novotny <xnovm149@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-04-01 14:19 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-04-01 13:13 Summing of INTERVAL returns incorrect number of days and hours Milan Novotny <xnovm149@gmail.com>
2025-04-01 14:19 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox