TIPS：MySQL按给定时间间隔分组统计

embedded/2024/9/23 9:26:58/

MySQL按给定时间间隔分组统计

需求一，查询每天8点到23点的数据

在MySQL中，若要查询在一个指定时间段内（比如每天的8点到23点）的所有数据记录，可以使用BETWEEN关键字结合日期函数来实现。假设数据表名为your_table_name，时间字段名为time_column。以下是一个示例SQL查询语句，获取每天8点到23点之间的所有数据记录：

SELECT * 
FROM your_table_name 
WHERE HOUR(time_column) BETWEEN 8 AND 23;

上述查询直接基于时间字段的小时部分进行筛选，但是需要注意的是，time_column是一个可以直接用HOUR()函数提取小时数的日期时间类型字段（如DATETIME或TIMESTAMP类型）。

需求二，查询指定时间段内，每天8点到23点的数据

如果想要查询指定时间段内的每天8点到23点的所有数据，那么在上述SQL再加上时间查询语句即可：

SELECT * 
FROM your_table_name 
WHERE time_column BETWEEN '2024-04-02 00:00:00' AND  '2024-04-06 23:59:59'
AND HOUR(time_column) BETWEEN 8 AND 23;

需求三，按照时间间隔分组统计

如果想要查询指定时间段内的每天8点到23点的所有数据，并在按照指定秒钟数时间间隔统计，假如每隔5秒钟进行统计：

SELECT CONCAT(DATE_FORMAT(create_time, '%Y-%m-%d %H:%i:'), LPAD(floor(second(create_time)/ 5)*5, 2, '0')) timePoint,COUNT(*) totalCount
FROM your_table_name 
WHERE time_column BETWEEN '2024-04-02 00:00:00' AND  '2024-04-06 23:59:59'
AND HOUR(time_column) BETWEEN 8 AND 23
GROUP BYtimePoint;

CONCAT(DATE_FORMAT(create_time, '%Y-%m-%d %H:%i:'), LPAD(floor(second(create_time)/ 5)*5, 2, '0'))语句中的5就表示每间隔5秒钟进行分组统计，如果想要其它间隔时间直接修改即可。

需求四，分组统计某个时间点的数据

上述SQL已经按照每间隔5秒钟进行了分组统计，那么如果现在又只想统计每个时间间隔的数据，又该怎样编写SQL呢：

SELECT CONCAT(DATE_FORMAT(create_time, '%Y-%m-%d %H:%i:'), LPAD(floor(second(create_time)/ 5)*5, 2, '0')) timePoint,COUNT(*) totalCount
FROM your_table_name 
WHERE time_column BETWEEN '2024-04-02 00:00:00' AND  '2024-04-06 23:59:59'
AND HOUR(time_column) BETWEEN 8 AND 23
AND second(time_column) % 5 = 0
GROUP BYtimePoint;