Hourly counting based on start time and end time data-PHP Chinese Network Q&A

Article Topic Learning Download Q&A Programming Dictionary Game Recent Updates

简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)

Hourly counting based on start time and end time data

P粉283559033 2023-08-30 15:53:41

366

In the table, the data is in timestamp format but I am sharing it in time(start_at) and 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 the result, we need to count the number of active ids between the start_at and end_at times.

hours, count 0, 0 1, 1 twenty two 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

P粉283559033

reply all (1)

P粉4329300812023-08-31 09:26:55 1 floor

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 first query returns the hour column in time format, while the second query returns this column in numeric form. Choose the variant that is safe for you.

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