下面的 SQL 可以按照每分钟的 0,5,10... 秒为单位统计。没有数据的时间段需要在程序补齐。
SELECT
DATE_ADD(created_at, INTERVAL - SECOND(created_at) % 5 SECOND) AS TheDate,
COUNT(1) as Count
FROM
table_name
WHERE
created_at > '2021-09-01 00:00:00'
GROUP BY
DATE_ADD(created_at, INTERVAL - SECOND(created_at) % 5 SECOND)
ORDER BY
DATE_ADD(created_at, INTERVAL - SECOND(created_at) % 5 SECOND);
TheDate | Count |
2021-09-07 13:42:50 | 1 |
2021-09-07 14:42:55 | 5 |
2021-09-07 16:48:15 | 1 |
2021-09-07 16:54:45 | 1 |
2021-09-07 16:56:25 | 3 |
2021-09-07 17:56:45 | 2 |
2021-09-07 17:57:30 | 1 |