MySQL按照小时,日,月,年等条件查询
在地灾系统中雨量历史数据查询条件有5分钟,30分钟,1小时,3小时,6小时,12小时和24小时,原本的查询语句为:
if(ylzb.equals("0")){//查询间隔5分钟
sql = "select sensorData1,DATE_FORMAT(sendTime,'%Y-%m-%d %H:%i') "
+ "from `data" + eNum + "` e where sendTime>='" + startTime
+ "' and sendTime<='" + endTime + "' ORDER BY e.sendTime;";
}else if(ylzb.equals("0.5")){//查询间隔30分钟
sql = "select sum(sensorData1),DATE_FORMAT(sendTime,'%Y-%m-%d %H:%i') "
+ "from `data" + eNum + "` e where sendTime>='" + startTime
+ "' and sendTime<='"
+ endTime + "' GROUP BY FLOOR(MINUTE(e.sendTime)/30),HOUR(e.sendTime)"
+ " ORDER BY e.sendTime;";
}else{//查询间隔为ylzb小时
sql = "select sum(sensorData1),DATE_FORMAT(sendTime,'%Y-%m-%d %H:%i') "
+ "from `data" + eNum + "` e where sendTime>='" + startTime
+ "' and sendTime<='" + endTime + "' GROUP BY FLOOR(HOUR(e.sendTime)/"+ylzb
+ ") ORDER BY e.sendTime;";
}
假设查询2018/8/1~2018/8/16这段时间的数据,当ylzb等于0.5或者0时数据能够正常返回,但等于其他值时只会返回第一天的数据,时间间隔为ylzb。
为解决此问题查询MySQL按照n分钟,n小时,n天,n年等查询问题。
问题原因是最后一个查询语句GROUP BY里面缺少年月日。
当时间间隔单位为1时。可直接用如下语句查询:
//按1小时查询
select sum(sensorData1) ,DATE_FORMAT(sendTime,'%Y-%m-%d %H:%i') from `data0028-gx-1a04001` e
where sendTime>='2018-08-01 00:00:00' and sendTime <= '2018-08-16 23:59:59'
GROUP BY DATE_FORMAT(sendTime,'%Y-%m-%d %H') ;
//按1天查询
select sum(sensorData1) ,DATE_FORMAT(sendTime,'%Y-%m-%d %H:%i') from `data0028-gx-1a04001` e
where sendTime>='2018-08-01 00:00:00' and sendTime <= '2018-08-16 23:59:59'
GROUP BY DATE_FORMAT(sendTime,'%Y-%m-%d') ;
//按1月查询
select sum(sensorData1) ,DATE_FORMAT(sendTime,'%Y-%m-%d %H:%i') from `data0028-gx-1a04001` e
where sendTime>='2018-08-01 00:00:00' and sendTime <= '2018-08-16 23:59:59'
GROUP BY DATE_FORMAT(sendTime,'%Y-%m') ;
//按1年查询
select sum(sensorData1) ,DATE_FORMAT(sendTime,'%Y-%m-%d %H:%i') from `data0028-gx-1a04001` e
where sendTime>='2018-08-01 00:00:00' and sendTime <= '2018-08-16 23:59:59'
GROUP BY DATE_FORMAT(sendTime,'%Y') ;
当时间间隔单位不为1,比如6小时,此刻可以用DIV或者FLOOR作为时间分割,代码如下:
select cast(sum(sensorData1) AS DECIMAL(19,2)) ,DATE_FORMAT(sendTime,'%Y-%m-%d %H:%i')
from `data0028-gx-1a04001` e where sendTime>='2018-08-01 00:00:00'
and sendTime <= '2018-08-16 23:59:59'
GROUP BY DATE_FORMAT(sendTime,'%y-%m-%d') ,HOUR(e.sendTime) DIV 6 ;
或者
select cast(sum(sensorData1) AS DECIMAL(19,2)) ,DATE_FORMAT(sendTime,'%Y-%m-%d %H:%i')
from `data0028-gx-1a04001` e where sendTime>='2018-08-01 00:00:00'
and sendTime <= '2018-08-16 23:59:59'
GROUP BY DATE_FORMAT(sendTime,'%y-%m-%d') ,FLOOR(HOUR(e.sendTime)/6);
搜索结果为(两个查询语句的结果一样):
这里需要注意两个问题:
cast和DECIMAL
1.cast在这里的作用为使得sensordata1可以转换为固定的浮点格式增加,以免结果小数位数发生错误
2.DECIMAL是设置格式的作用,DECIMAL(a,b)中a表示有效数字的精度,即这个数字一共有多少位;b表示小数点的位数。如果不这样设置,而是直接写sum(sensorData1),有的结果会有很多的小数位。如图所示:
年月日表达格式
%S, %s 两位数字形式的秒( 00,01, …, 59)
%i 两位数字形式的分( 00,01, …, 59)
%H 两位数字形式的小时,24 小时(00,01, …, 23)
%h, %I 两位数字形式的小时,12 小时(01,02, …, 12)
%k 数字形式的小时,24 小时(0,1, …, 23)
%l 数字形式的小时,12 小时(1, 2, …, 12)
%T 24 小时的时间形式(hh : mm : s s)
%r 12 小时的时间形式(hh:mm:ss AM 或hh:mm:ss PM)
%p AM 或P M
%W 一周中每一天的名称( Sunday, Monday, …, Saturday)
%a 一周中每一天名称的缩写( Sun, Mon, …, Sat)
%d 两位数字表示月中的天数( 00, 01, …, 31)
%e 数字形式表示月中的天数( 1, 2, …, 31)
%D 英文后缀表示月中的天数( 1st, 2nd, 3rd, …)
%w 以数字形式表示周中的天数( 0 = Sunday, 1=Monday, …, 6=Saturday)
%j 以三位数字表示年中的天数( 001, 002, …, 366)
% U 周(0, 1, 52),其中Sunday 为周中的第一天
%u 周(0, 1, 52),其中Monday 为周中的第一天
%M 月名(January, February, …, December)
%b 缩写的月名( January, February, …, December)
%m 两位数字表示的月份( 01, 02, …, 12)
%c 数字表示的月份( 1, 2, …, 12)
%Y 四位数字表示的年份
%y 两位数字表示的年份
%% 直接值“%”
编写查询语句的时候因为%H和%h的问题弄了好久,还因为用了%y看着查询结果年份只有两位数惊到了 T T