按小时查看
select COUNT(1),
DATE_FORMAT(CREATE_TIME,'%Y-%m-%d %H')
from itm_app_log_monitor_bzxr a group by DATE_FORMAT(CREATE_TIME ,'%Y-%m-%d %H');
mysql按半小时分组统计
select sum(revNum),dataStartTime from (
select revNum , DATE_FORMAT( concat(date(dataStartTime),' ',hour(dataStartTime),':',floor( minute(dataStartTime)/30 )*30) ,'%Y-%m-%d %H:%i') as dataStartTime from devicedata
) a
group by DATE_FORMAT( dataStartTime ,'%Y-%m-%d %H:%i')
注释:
想法是将 hh:00-hh:29的数据显示成hh:00,hh:30-hh:59的数据显示成hh:30 , 然后在group by就好了