mysql统计

• Z时代
• 2024-01-10
• 分类：综合

-- 时间转任意格式DATE_FORMAT
-- #select DATE_FORMAT(NOW(),"%m-%d-%Y");
-- unix_timestamp 时间转时间戳
-- select unix_timestamp(now());  
-- from_unixtime 时间戳转时间
-- SELECT from_unixtime(created_time,"%m-%d-%Y") 
#按天统计
-- SELECT from_unixtime(created_time,"%m-%d-%Y") AS date, COUNT(*) AS num
-- FROM filter_ip
-- GROUP BY date
-- ORDER BY date;
#按小时统计
-- SELECT from_unixtime(created_time, "%Y-%m-%d %H:00:00") AS time, COUNT(*) AS num
-- FROM filter_ip
-- GROUP BY time
-- ORDER BY time;
#按分钟统计
-- SELECT DATE_FORMAT(from_unixtime(created_time), "%Y-%m-%d %H:%i:00") AS time, COUNT(*) AS num
-- FROM filter_ip 
-- GROUP BY time
-- ORDER BY time;
#按十分钟统计、按半小时统计只需要把10改为30
SELECT time, COUNT( * ) AS num 
FROM
	(
	SELECT 
		DATE_FORMAT(
			concat( date( from_unixtime(created_time) ), " ", HOUR ( from_unixtime(created_time) ), ":", floor( MINUTE ( from_unixtime(created_time) ) / 10 ) * 10 ),
			"%Y-%m-%d %H:%i" 
		) AS time 
	FROM filter_ip
	) a 
GROUP BY DATE_FORMAT( time, "%Y-%m-%d %H:%i" ) 
ORDER BY time;

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

以上是 mysql统计 的全部内容， 来源链接： utcz.com/z/533346.html