By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
select version();
| version |
|---|
| PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit |
SELECT 1
-- your query with MIN(date) instead of MAX(date).
WITH RECURSIVE date_range AS (
SELECT '2023-01-02 10:34:36'::timestamp AS date
UNION ALL
SELECT CASE
WHEN EXTRACT(ISODOW FROM date) IN (6, 7) THEN date + INTERVAL '1 day'*(8-EXTRACT(ISODOW FROM date))
ELSE date + INTERVAL '1 DAY'
END
FROM date_range
WHERE date + INTERVAL '1 DAY' < '2023-01-10 15:12:24'::timestamp
)
SELECT
CONCAT(
FLOOR(diff / 86400), ' days ',
FLOOR((diff % 86400) / 3600), ' hours ',
FLOOR((diff % 3600) / 60), ' minutes ',
FLOOR(diff % 60), ' seconds'
) AS duration
FROM (
SELECT
EXTRACT(EPOCH FROM ('2023-01-10 15:12:24'::timestamp - min(date))::interval) AS diff
FROM date_range) t;
| duration |
|---|
| 8 days 4 hours 37 minutes 48 seconds |
SELECT 1
-- Just use date subtraction and extract from resulting interval
with date_range(diff) as
(select '2023-01-10 15:12:24'::timestamp - '2023-01-02 10:34:36'::timestamp)
select CONCAT(
extract( day from diff) , ' days '
, extract( hours from diff) , ' hours '
, extract( minutes from diff) , ' minuets '
, extract( seconds from diff)::int , ' seconds ') AS duration
from date_range;
| duration |
|---|
| 8 days 4 hours 37 minuets 48 seconds |
SELECT 1
-- Just get diff as interval
select '2023-01-10 15:12:24'::timestamp - '2023-01-02 10:34:36'::timestamp AS duration;
| duration |
|---|
| 8 days 04:37:48 |
SELECT 1