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?.
SET intervalstyle = 'postgres_verbose';
WITH date_range AS
(SELECT '2023-01-02 10:34:36'::timestamp AS start_date
, '2023-01-10 15:12:24'::timestamp AS end_date),
date_series AS
(SELECT d as date
, (CASE
WHEN start_date::date = d THEN interval '1 day' + (d - start_date)::interval
WHEN end_date::date = d THEN (end_date - d)::interval
ELSE interval '1 day'
END) AS working_time
FROM date_range
CROSS JOIN generate_series(start_date::date, end_date::timestamp, interval '1 day') d
WHERE EXTRACT (ISODOW FROM d) BETWEEN 1 AND 5)
SELECT COALESCE(SUM(working_time), interval '0 day') AS working_time
FROM date_series;
SET
| working_time |
|---|
| @ 6 days 4 hours 37 mins 48 secs |
SELECT 1