mysql统计

1-- 时间转任意格式DATE_FORMAT
2-- #select DATE_FORMAT(NOW(),'%m-%d-%Y');
3-- unix_timestamp 时间转时间戳
4-- select unix_timestamp(now());  
5-- from_unixtime 时间戳转时间
6-- SELECT from_unixtime(created_time,'%m-%d-%Y') 
7
8#按天统计
9-- SELECT from_unixtime(created_time,'%m-%d-%Y') AS date, COUNT(*) AS num
10-- FROM filter_ip
11-- GROUP BY date
12-- ORDER BY date;
13
14
15#按小时统计
16-- SELECT from_unixtime(created_time, '%Y-%m-%d %H:00:00') AS time, COUNT(*) AS num
17-- FROM filter_ip
18-- GROUP BY time
19-- ORDER BY time;
20
21#按分钟统计
22-- SELECT DATE_FORMAT(from_unixtime(created_time), '%Y-%m-%d %H:%i:00') AS time, COUNT(*) AS num
23-- FROM filter_ip 
24-- GROUP BY time
25-- ORDER BY time;
26
27#按十分钟统计、按半小时统计只需要把10改为30
28
29SELECT time, COUNT( * ) AS num 
30FROM
31	(
32	SELECT 
33		DATE_FORMAT(
34			concat( date( from_unixtime(created_time) ), ' ', HOUR ( from_unixtime(created_time) ), ':', floor( MINUTE ( from_unixtime(created_time) ) / 10 ) * 10 ),
35			'%Y-%m-%d %H:%i' 
36		) AS time 
37	FROM filter_ip
38	) a 
39GROUP BY DATE_FORMAT( time, '%Y-%m-%d %H:%i' ) 
40ORDER BY time;

参考：https://blog.csdn.net/u010946448/article/details/83752984