myaql数据按15分/30分/1小时分组统计或查询

糯米黑子

于 2023-05-05 10:46:37 发布

阅读量209

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/qq_52031916/article/details/130501703

版权

我本地的时间为时间戳，如果是正常的时间可以把

FROM_UNIXTIME(wa.create_time/1000,'%Y-%m-%d %H:%i:%s')替换为你们的时间

这是1小时的分组

 select cast(concat(substr(date_add(date_sub(FROM_UNIXTIME(wa.create_time/1000,'%Y-%m-%d %H:%i:%s'), INTERVAL minute(FROM_UNIXTIME(wa.create_time/1000,'%Y-%m-%d %H:%i:%s')) minute), INTERVAL floor(minute(FROM_UNIXTIME(wa.create_time/1000,'%Y-%m-%d %H:%i:%s')) / 60) * 60 minute),1, 17), '00') as datetime) hours,count(1) num FROM warning wa left join section se on wa.section_id = se.id 
where FROM_UNIXTIME(wa.create_time/1000,'%Y-%m-%d') = '2022-11-09'
 group by cast(concat(substr(date_add(date_sub(FROM_UNIXTIME(create_time/1000,'%Y-%m-%d %H:%i:%s'), INTERVAL minute(FROM_UNIXTIME(create_time/1000,'%Y-%m-%d %H:%i:%s')) minute), INTERVAL floor(minute(FROM_UNIXTIME(create_time/1000,'%Y-%m-%d %H:%i:%s')) / 60) * 60 minute),1, 17), '00') as datetime);

对应的如果想要以30分钟分组的话可以把里面的60换成30

select cast(concat(substr(date_add(date_sub(FROM_UNIXTIME(wa.create_time/1000,'%Y-%m-%d %H:%i:%s'), INTERVAL minute(FROM_UNIXTIME(wa.create_time/1000,'%Y-%m-%d %H:%i:%s')) minute), INTERVAL floor(minute(FROM_UNIXTIME(wa.create_time/1000,'%Y-%m-%d %H:%i:%s')) / 30) * 30 minute),1, 17), '00') as datetime) hours,count(1) num FROM warning wa left join section se on wa.section_id = se.id 
where FROM_UNIXTIME(wa.create_time/1000,'%Y-%m-%d') = '2022-11-09'
 group by cast(concat(substr(date_add(date_sub(FROM_UNIXTIME(create_time/1000,'%Y-%m-%d %H:%i:%s'), INTERVAL minute(FROM_UNIXTIME(create_time/1000,'%Y-%m-%d %H:%i:%s')) minute), INTERVAL floor(minute(FROM_UNIXTIME(create_time/1000,'%Y-%m-%d %H:%i:%s')) / 30) * 30 minute),1, 17), '00') as datetime);