从history表获取数据改为从history天表获取数据

kaiyu

2020-11-18 4fbbd4ff938875997a84914c0310149851ced122

从history表获取数据改为从history天表获取数据

 src/main/java/com/moral/task/HistoryTableInsertTask.java

@@ -62,31 +62,31 @@

    @XxlJob("historyMinutely")
    public ReturnT insertHistoryMinutelyTable(String params) {
        LocalDateTime time = LocalDateTime.now();
        int year = time.getYear();
        int month = time.getMonthValue();
        int day = time.getDayOfMonth();
        int hour = time.getHour();
        int minute = time.getMinute();
        if (day == 1) {
            if (hour == 0 && minute == 0) {
                if (month == 1) {
                    month = 12;
                    year = year - 1;
                } else {
                    month = month - 1;
                }
            }
        }
        String monthStr = month < 10 ? ("0" + month) : month + "";
        String yearAndMonth = year + monthStr;
        LocalDateTime endTime = time.truncatedTo(ChronoUnit.MINUTES);
        LocalDateTime startTime = endTime.minusMinutes(1);
        List<String> sensorKeys = sensorService.getSensorKeys();
        //正式环境使用
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:00");
        Date endTimeDate = new Date();
        Date startTimeDate = DateUtil.rollMinute(endTimeDate, -1);
        StringBuilder endTime = new StringBuilder(sdf.format(endTimeDate));
        StringBuilder startTime = new StringBuilder(sdf.format(startTimeDate));
        String queryYearAndMonthDay = DateUtil.getYear(startTimeDate) + DateUtil.getMonth(startTimeDate) + DateUtil.getDay(startTimeDate);

        //测试试用
        /*SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date endTimeDate = sdf.parse("2020-11-01 00:00:20");
        Date startTimeDate = DateUtil.rollMinute(endTimeDate, -1);
        StringBuilder endTime = new StringBuilder(sdf.format(endTimeDate));
        StringBuilder startTime = new StringBuilder(sdf.format(startTimeDate));
        endTime.replace(17, 19, "00");
        startTime.replace(17, 19, "00");
        String queryYearAndMonthDay = DateUtil.getYear(startTimeDate) + DateUtil.getMonth(startTimeDate) + DateUtil.getDay(startTimeDate);*/

        Map<String, Object> devices = new HashMap<>();
        List<String> sensorKeys = sensorService.getSensorKeys();
        devices.put("sensorKeys", sensorKeys);
        devices.put("start", startTime);
        devices.put("end", endTime);
        devices.put("start", startTime.toString());
        devices.put("end", endTime.toString());
        devices.put("yearAndMonthDay", queryYearAndMonthDay);

        try {
            List<Map<String, Object>> minutelyData = deviceService.getSensorData(devices);
            XxlJobLogger.log("historyMinutelyData:" + minutelyData.size());
@@ -96,7 +96,7 @@
                    Map<String, Object> minutelyDataMap = new LinkedHashMap<>();
                    JSONObject jo = new JSONObject(true);
                    minutelyDataMap.put("mac", deviceData.get("mac"));
                    minutelyDataMap.put("time", startTime);
                    minutelyDataMap.put("time", endTime.toString());
                    JSONArray jsonArray = new JSONArray();
                    for (String key : deviceData.keySet()) {
                        if (!key.equals("mac") && !key.startsWith("M")) {
@@ -113,7 +113,8 @@
                }
            }
            if (!CollectionUtils.isEmpty(minutelyDataList)) {
                historyMinutelyService.insertHistoryMinutely(minutelyDataList, yearAndMonth);
                String insertYearAndMonth = DateUtil.getYear(endTimeDate) + DateUtil.getMonth(endTimeDate);
                historyMinutelyService.insertHistoryMinutely(minutelyDataList, insertYearAndMonth);
                ReturnT returnT = new ReturnT(200, "插入分钟表成功");
                return returnT;
            }

 src/main/resources/mapper/HistoryMapper.xml

@@ -107,7 +107,7 @@
            </choose>

        </foreach>

        FROM

        history h

        history_${yearAndMonthDay} h

        WHERE

        h.time >= #{start}

        AND h.time <![CDATA[<]]> #{end}