count by hours in between with start and end time data-CodePudding

In table data is in Timestamp format, but I shared it in Date(start_at), Date(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

CodePudding user response：

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;

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