mysql 分钟分组_mysql按天，小时，分钟进行数据分组统计

按天统计

SELECT DATE(TimeStart) AS date, COUNT(*) AS num

FROM track

WHERE Flag = 0 AND Duration >= 300

GROUP BY date

ORDER BY date;

按小时统计

SELECT DATE_FORMAT(TimeStart, '%Y-%m-%d %H:00:00') AS time, COUNT(*) AS num

FROM track

WHERE Flag = 0 AND Duration >= 300

GROUP BY time

ORDER BY time;

按半小时统计

SELECT time, COUNT( * ) AS num

FROM

(

SELECT Duration,

DATE_FORMAT(

concat( date( TimeStart ), ' ', HOUR ( TimeStart ), ':', floor( MINUTE ( TimeStart ) / 30 ) * 30 ),

'%Y-%m-%d %H:%i'

) AS time

FROM tarck

WHERE Flag = 0 AND Duration >= 300

) a

GROUP BY DATE_FORMAT( time, '%Y-%m-%d %H:%i' )

ORDER BY time;

按N分钟统计

SELECT time, COUNT( * ) AS num

FROM

(

SELECT Duration,

DATE_FORMAT(

concat( date( TimeStart ), ' ', HOUR ( TimeStart ), ':', floor( MINUTE ( TimeStart ) / 10 ) * 10 ),

'%Y-%m-%d %H:%i'

) AS time

FROM tarck

WHERE Flag = 0 AND Duration >= 300

) a

GROUP BY DATE_FORMAT( time, '%Y-%m-%d %H:%i' )

ORDER BY time;

按分钟统计

SELECT DATE_FORMAT(TimeStart, '%Y-%m-%d %H:%i:00') AS time, COUNT(*) AS num

FROM track

WHERE Flag = 0 AND Duration >= 300

GROUP BY time

ORDER BY time;