mysql按时间分组统计
2019-09-25 本文已影响0人
博陵韩少
user表
| user | age | sex | update_time |
|---|---|---|---|
| Lin | 20 | Male | 2019-09-23 13:57:00 |
| Andy | 22 | Female | 2019-09-23 13:58:00 |
| Lucy | 21 | Female | 2019-09-24 13:30:00 |
- 按天统计
select DATE(update_time) AS mydate,count(*) AS num
from user
group by mydate
order by mydate;
- 按小时统计
select DATE_FORMAT(update_time, '%Y-%m-%d %H:00:00') AS mytime,count(*) AS num
from user
group by mytime
order by mytime;
- 按半小时统计
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;
- 按分钟统计
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
