sql按时间分段的关键逻辑是将某个时间段内的时间归一化到某个时间点,代码上表现得很清晰
按小时查询
SELECT COUNT(*), DATE_FORMAT(time ,'%Y-%m-%d %H:00:00') as time
FROM table
GROUP BY time
按分钟查询
SELECT COUNT(*), DATE_FORMAT(time ,'%Y-%m-%d %H:%i:00') as time
FROM table
GROUP BY time
按自定义分钟(如半小时)
SELECT COUNT(*),DATE_FORMAT(
concat( date(create_time ), ' ', HOUR (create_time ), ':', floor( MINUTE (create_time) / 30 ) * 30 ), '%Y-%m-%d %H:%i' ) AS time
FROM table
GROUP BY time