SELECT time, COUNT( * ) AS num
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
GROUP BY DATE_FORMAT( time, '%Y-%m-%d %H:%i' )
ORDER BY time;
-
1
-
2
-
3
-
4
-
5
-
6
-
7
-
8
-
9
-
10
-
11
-
12
-
13
结果如下：
将上面的SQL语句稍微修改下，就可以实现按任意N分钟为时间片的分组统计，如按10分钟统计，先上代码：
SELECT time, COUNT( * ) AS num
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
GROUP BY DATE_FORMAT( time, '%Y-%m-%d %H:%i' )
ORDER BY time;
-
1
-
2
-
3
-
4
-
5
-
6
-
7
-
8
-
9
-
10
-
11
-
12
-
13
基本思路：
将datetime类型的时间转化为相应时间片的时间，例如将‘2017-03-01 01:08:19’ 转化为‘2017-03-01 01:00:00’，然后group by即可。
<link href="https://csdnimg.cn/release/phoenix/mdeditor/markdown_views-e44c3c0e64.css" rel="stylesheet">
