MySQL按小时，日，月，年等条件统计查询

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