MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE test (id INT, start_at TIME, end_at TIME);
INSERT INTO test VALUES
(1,  '03:00:00', '06:00:00'),
(2,  '02:00:00', '05:00:00'),
(3,  '01:00:00', '08:00:00'),
(4,  '08:00:00', '13:00:00'),
(5,  '09:00:00', '21:00:00'),
(6,  '13:00:00', '16:00:00'),
(7,  '15:00:00', '19:00:00');
SELECT * FROM test;

Records: 7  Duplicates: 0  Warnings: 0




id
start_at
end_at


1
03:00:00
06:00:00

2
02:00:00
05:00:00

3
01:00:00
08:00:00

4
08:00:00
13:00:00

5
09:00:00
21:00:00

6
13:00:00
16:00:00

7
15:00:00
19:00:00


WITH RECURSIVE
cte AS (
    SELECT CAST('00:00:00' AS TIME) `hour`
    UNION ALL
    SELECT `hour` + INTERVAL 1 HOUR FROM cte WHERE `hour` < '23:00:00'
)
SELECT cte.`hour`, COUNT(test.id) `count`
FROM cte 
LEFT JOIN test ON cte.`hour` >= test.start_at 
              AND cte.`hour` < test.end_at 
GROUP BY 1
ORDER BY 1;


hour
count


00:00:00
0

01:00:00
1

02:00:00
2

03:00:00
3

04:00:00
3

05:00:00
2

06:00:00
1

07:00:00
1

08:00:00
1

09:00:00
2

10:00:00
2

11:00:00
2

12:00:00
2

13:00:00
2

14:00:00
2

15:00:00
3

16:00:00
2

17:00:00
2

18:00:00
2

19:00:00
1

20:00:00
1

21:00:00
0

22:00:00
0

23:00:00
0


WITH RECURSIVE
cte AS (
    SELECT 0 `hour`
    UNION ALL
    SELECT `hour` + 1 FROM cte WHERE `hour` < 23
)
SELECT cte.`hour`, COUNT(test.id) `count`
FROM cte 
LEFT JOIN test ON cte.`hour` >= HOUR(test.start_at)
              AND cte.`hour` < HOUR(test.end_at)
GROUP BY 1
ORDER BY 1;


hour
count


0
0

1
1

2
2

3
3

4
3

5
2

6
1

7
1

8
1

9
2

10
2

11
2

12
2

13
2

14
2

15
3

16
2

17
2

18
2

19
1

20
1

21
0

22
0

23
0

id	start_at	end_at
1	03:00:00	06:00:00
2	02:00:00	05:00:00
3	01:00:00	08:00:00
4	08:00:00	13:00:00
5	09:00:00	21:00:00
6	13:00:00	16:00:00
7	15:00:00	19:00:00

hour	count
00:00:00	0
01:00:00	1
02:00:00	2
03:00:00	3
04:00:00	3
05:00:00	2
06:00:00	1
07:00:00	1
08:00:00	1
09:00:00	2
10:00:00	2
11:00:00	2
12:00:00	2
13:00:00	2
14:00:00	2
15:00:00	3
16:00:00	2
17:00:00	2
18:00:00	2
19:00:00	1
20:00:00	1
21:00:00	0
22:00:00	0
23:00:00	0

hour	count
0	0
1	1
2	2
3	3
4	3
5	2
6	1
7	1
8	1
9	2
10	2
11	2
12	2
13	2
14	2
15	3
16	2
17	2
18	2
19	1
20	1
21	0
22	0
23	0

hour	count
0	0
1	1
2	2
3	3
4	3
5	2
6	1
7	1
8	1
9	2
10	2
11	2
12	2
13	2
14	2
15	3
16	2
17	2
18	2
19	1
20	1
21	0
22	0
23	0

hour	count
0	0
1	1
2	2
3	3
4	3
5	2
6	1
7	1
8	1
9	2
10	2
11	2
12	2
13	2
14	2
15	3
16	2
17	2
18	2
19	1
20	1
21	0
22	0
23	0