Postgres 14

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?.
CREATE TABLE t (t_id int PRIMARY KEY, t_start timestamp, t_end timestamp);
INSERT INTO t VALUES 
  (1, '2009-12-03 14:00', '2009-12-04 09:00')
, (2, '2009-12-03 15:00', '2009-12-07 08:00')  -- examples in question
, (3, '2013-06-24 07:00', '2013-06-24 12:00')
, (4, '2013-06-24 12:00', '2013-06-24 23:00')
, (5, '2013-06-23 13:00', '2013-06-25 11:00')
, (6, '2013-06-23 14:01', '2013-06-24 08:59')  -- max. fractions at begin and end
;

CREATE TABLE



INSERT 0 6


-- Simple version for Postgres 9.2 or older
-- 1-hour precision
SELECT t_id, count(*) AS work_hours
FROM  (
   SELECT t_id, generate_series (t_start, t_end - interval '1h', interval '1h') AS h
   FROM   t
   ) sub
WHERE  EXTRACT(ISODOW FROM h) < 6
AND    h::time >= '08:00'
AND    h::time <= '14:00'
GROUP  BY 1
ORDER  BY 1;


t_id
work_hours


1
2

2
7

3
4

4
3

5
10



SELECT 5


-- 5-min precision
SELECT t_id, (count(*) * interval '5 min')::text AS work_interval
FROM  (
   SELECT t_id, generate_series (t_start, t_end - interval '5 min', interval '5 min') AS h
   FROM   t
   ) sub
WHERE  EXTRACT(ISODOW FROM h) < 6
AND    h::time >= '08:00'
AND    h::time <= '14:55'  -- 15.00 - interval '5 min'
GROUP  BY 1
ORDER  BY 1;


t_id
work_interval


1
02:00:00

2
07:00:00

3
04:00:00

4
03:00:00

5
10:00:00

6
00:55:00



SELECT 6


-- LATERAL in Postgres 9.3+
-- 1-hour precision
SELECT t.t_id, h.work_hours
FROM   t
LEFT   JOIN LATERAL (
   SELECT count(*) AS work_hours
   FROM   generate_series (t.t_start, t.t_end - interval '1h', interval '1h') h
   WHERE  EXTRACT(ISODOW FROM h) < 6
   AND    h::time >= '08:00'
   AND    h::time <= '14:00'
   ) h ON TRUE
ORDER  BY 1;


t_id
work_hours


1
2

2
7

3
4

4
3

5
10

6
0



SELECT 6


-- 5-min precision
SELECT t.t_id, h.work_interval
FROM   t
LEFT   JOIN LATERAL (
   SELECT (count(*) * interval '5 min')::text AS work_interval
   FROM   generate_series (t.t_start, t.t_end - interval '5 min', interval '5 min') h
   WHERE  EXTRACT(ISODOW FROM h) < 6
   AND    h::time >= '08:00'
   AND    h::time <= '14:55'
   ) h ON TRUE
ORDER  BY 1;


t_id
work_interval


1
02:00:00

2
07:00:00

3
04:00:00

4
03:00:00

5
10:00:00

6
00:55:00



SELECT 6