使用MySQL的DATE

对数据按照创建时间进行图表统计，按照年or月or日

使用MySQL 的DATE_FORMAT函数进行筛选
参考链接1

1.controller层

@PostMapping("/getAlumniTime")
    public ResultVO getAlumniTime(HttpServletRequest request, @RequestBody String param) {
        SystemUser user = sysUserUtil.getUser(request);
        HashMap<String, Object> conditionMap = new HashMap<>();
      
        List<String> yearData = new ArrayList<>();
        List<Integer> regAdd = new ArrayList<>();//数据
        JSONObject jsonObject = new JSONObject();
       switch (time) {
            case "year":
                List<Count> countListY = regAlumniuserService.getAlumniTime(conditionMap, user.getOrganizationId());
                conditionMap.put("time", "year");
                for (Integer i = 1; i <= 12; i++) {
                    String month = i + "月";
                    regAdd.add(i - 1, 0);
                    if (countListY.size() > 0) {
                        for (Count cy : countListY) {
                            if ((cy.getSchName().equals(i.toString()))) {
                                regAdd.add(i - 1, cy.getSchId());
                            }
                        }
                    }
                    yearData.add(month);
                }

                jsonObject.put("time", yearData);
                jsonObject.put("data", regAdd);
                return new ResultVO(ResultCode.SUCCESS, jsonObject.toString());

            case "month":
                conditionMap.put("time", "month");
                Calendar a = Calendar.getInstance();//获取当前日期
                a.set(Calendar.DATE, 1);//
                a.roll(Calendar.DATE, -1);
                int maxDate = a.get(Calendar.DATE);
                List<Count> countListM = regAlumniuserService.getAlumniTime(conditionMap, user.getOrganizationId());
                for (Integer i = 1; i <= maxDate; i++) {
                    String day = i + "日";
                    regAdd.add(i - 1, 0);
                    if (countListM.size() > 0) {
                        for (Count cy : countListM) {
                            if ((cy.getSchName().equals(i.toString()))) {
                                regAdd.add(i - 1, cy.getSchId());
                            }
                        }
                    }

                    yearData.add(day);
                }

                jsonObject.put("time", yearData);
                jsonObject.put("data", regAdd);
                return new ResultVO(ResultCode.SUCCESS, jsonObject.toString());

            default:
                conditionMap.put("time", "day");
                List<Count> countListD = regAlumniuserService.getAlumniTime(conditionMap, user.getOrganizationId());
                for (Integer i = 1; i <= 24; i++) {
                    String hour = i + "点";
                    regAdd.add(i - 1, 0);
                    if (countListD.size() > 0) {
                        for (Count cy : countListD) {
                            if ((cy.getSchName().equals(i.toString()))) {
                                regAdd.add(i - 1, cy.getSchId());
                            }
                        }
                    }
                    yearData.add(hour);

                }

                jsonObject.put("time", yearData);
                jsonObject.put("data", regAdd);
                return new ResultVO(ResultCode.SUCCESS, jsonObject.toString());
        }
    }

2.server和Mapper基本一致

create_tm 的数据库格式为 2021-09-07 15:23:41

3.对应的sql

//年
select  COUNT( rau.reg_alumniuserid ) AS schId , DATE_FORMAT( rau.create_tm ,'%c')AS schName 
from table 
where DATE_FORMAT(rau.create_tm, '%Y') = DATE_FORMAT(now(), '%Y')
group by schName  

//月
select  COUNT( rau.reg_alumniuserid ) AS schId , DATE_FORMAT( rau.create_tm ,'%e')AS schName 
from table 
where DATE_FORMAT(rau.create_tm, '%m') = DATE_FORMAT(now(), '%m')
group by schName  
//日
select  COUNT( rau.reg_alumniuserid ) AS schId , DATE_FORMAT( rau.create_tm ,'%k')AS schName 
from table 
where DATE_FORMAT(rau.create_tm, '%j') = DATE_FORMAT(now(), '%j')
group by schName