# [SOLVED] count by hours in between with start and end time data

## Issue

In table, data is in Timestamp format, but I shared it in Time(start_at), Time(end_at) format.

Table structure:

``````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
6,  15:00:00, 19:00:00
``````

For result we need to count ids which were active in between the start_at, end_at time.

``````hours, 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,      3
14,      2
15,      3
16,      2
17,      2
18,      2
19,      1
20,      1
21,      0
22,      0
23,      0
``````

## Solution

Either

``````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;
``````

or

``````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;
``````

The 1st query returns hours column in time format whereas the 2nd one returns numeric value for this column. Select the variant which is safe for you.

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=5a77b6e3158be06c7a551cb7e64673de