mysql按时间分组统计

user表

1. 按天统计

select DATE(update_time) AS mydate,count(*) AS num
from  user
group by mydate
order by mydate;

2. 按小时统计

select DATE_FORMAT(update_time, '%Y-%m-%d %H:00:00') AS mytime,count(*) AS num
from user
group by mytime
order by mytime;

3. 按半小时统计

SELECT time, COUNT( * ) AS num 
FROM
    (
    SELECT Duration,
        DATE_FORMAT(
            concat( date( update_time), ' ', HOUR ( update_time ), ':', floor( MINUTE ( update_time) / 30 ) * 30 ),
            '%Y-%m-%d %H:%i' 
        ) AS time 
    FROM tarck
    ) a 
GROUP BY DATE_FORMAT( time, '%Y-%m-%d %H:%i' ) 
ORDER BY time;

4. 按分钟统计

SELECT DATE_FORMAT(update_time, '%Y-%m-%d %H:%i:00') AS mytime, COUNT(*) AS num
FROM track 
GROUP BY mytime
ORDER BY mytime;

参考链接：https://blog.csdn.net/u010946448/article/details/83752984
参考链接：https://www.w3school.com.cn/sql/func_date_format.asp