mysql利用DATE_FORMAT函数进行时间区间分段查询

时间分段查询，例如查询一天内数据，划分为24小时，求出每小时内数据的平均值或者和。

实现方式，利用DATE_FORMAT(date,format)函数，对数据库中的时间字段进行格式化得出每天、每小时、每分钟… 然后通过group by xxxTimeField进行分组。

SQL

SELECT
	history.*,
	CEIL( AVG( history.VALUE ) ) AS avgval,
	SUM( history.VALUE ) AS sumval,
	DATE_FORMAT( FROM_UNIXTIME( history.clock, '%Y-%m-%d %H:%i:%s' ), '%Y-%m-%d %H:00' ) formattime 
FROM
	history
	LEFT JOIN items ON items.itemid = history.itemid 
WHERE
	history.itemid = items.itemid 
	AND history.clock >= 1586340120 
	AND history.clock <= 1586944920 
	AND items.hostid = 11454 
	AND FIND_IN_SET( items.key_, 'MEM_RATE,mem_usage,mem_per,system_resource_mem_until' ) 
GROUP BY
	formattime 
ORDER BY
	formattime

DATE_FORMAT( FROM_UNIXTIME( history.clock, ‘%Y-%m-%d %H:%i:%s’ ), ‘%Y-%m-%d %H:00’ ) formattime

表中的字段为clock，由于是时间戳，需要使用FROM_UNIXTIME函数格式化成Y-m-d H:i:s 格式。