按天统计
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;