mysql查询最近一周的数据量

DATE_SUB()函数     从日期减去指定的时间间隔

语法：

DATE_SUB(date,INTERVAL expr type)

date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。

type 参数可以是下列值：Type 值
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

示例：DATE_SUB(CURDATE(), INTERVAL 7 DAY)      获取一周前的日期

mybatis:

select sum(1) nums, DATE_FORMAT(t.create_time, '%Y-%m-%d') create_time
        from sys_verification_log t
        where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(t.create_time)
        group by DATE_FORMAT(t.create_time, '%Y-%m-%d');

dao接收：

List<Map<String,Object>> getVerificationNum();

注意：当springboot集成mybatis-plus/mybatis时，如果值为空时，属性也会没有，在application.properties文件中加入以下配置：

mybatis-plus.configuration.call-setters-on-nulls=true

 

service实现方法：

sql获取一周内的数据量，没有对应日期的则赋值为0，用于echarts图实现（展示近一周的数据量）

public List<Map<String, Object>> getVerificationNum() {
    return indexMapper.getVerificationNum();
}

 

public List weekCount(int flag) {//获取近一周的数据
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    List<Map<String,Object>> list=new ArrayList<>();
    List list2=new ArrayList<>();
    switch (flag){
        case 1:
            list=getVerificationNum();
            break;
        case 2:
            list=getShortmessageNum();
            break;
        case 3:
            list=getColorprintNum();
            break;
        case 4:
            list=getColorringInfoNum();
            break;
    }
    ArrayList<String> days=DateTestList.getDays(8);//获取最近一周的日期
    boolean status;//设置标志位，为True进行break，为false进行continu
    for(int i=0;i<days.size();i++){
        status=false;
        String day=days.get(i);
        if(list!=null&&!list.isEmpty()) {

            for(int h=0,len2=list.size();h<len2;h++){
                Map<String, Object> map = list.get(h);
                int nums=0;
                if(day.equals(map.get("create_time"))){
                    nums = Integer.parseInt(map.get("nums").toString());
                    list2.add(getLists(flag, nums, day));
                    status=true;
                    break;
                }
            }

        }
        if(status==false){
            list2.add(getLists(flag, 0, day));
            continue;
        }

    }
    return list2;
}



private Object getLists(int flag, int nums, String str) {
    if(flag==1){
        VerificationLog verificationLog=new VerificationLog(nums,str);
        return verificationLog;
    }else if(flag==2){
        ShortmessageLog shortmessageLog=new ShortmessageLog(nums,str);
        return shortmessageLog;
    }else if(flag==3){
        ColorprintLog colorprintLog=new ColorprintLog(nums,str);
        return colorprintLog;
    }else if(flag==4){
        ColorringInfo colorringInfo=new ColorringInfo(nums,str);
        return colorringInfo;
    }
    return null;
}

 

日期工具类方法：

public static ArrayList<String> getDays(int intervals) {
    ArrayList<String> pastDaysList = new ArrayList<>();
    for (int i = intervals -1; i >= 0; i--) {
        pastDaysList.add(getPastDate(i));
    }
    return pastDaysList;
}