From 2973f298e49fd8dff95065ef88d2997fe4eed338 Mon Sep 17 00:00:00 2001
From: kaiyu <404897439@qq.com>
Date: Fri, 16 Oct 2020 11:18:19 +0800
Subject: [PATCH] 建立五分钟数据表以及读取一分钟表数据计算平均值到五分钟表的接口
---
src/main/resources/mapper/HistoryFiveMinutelyMapper.xml | 35 +++++++
src/main/java/com/moral/service/impl/HistoryFiveMinutelyServiceImpl.java | 31 ++++++
src/main/java/com/moral/mapper/HistoryFiveMinutelyMapper.java | 15 +++
src/main/java/com/moral/util/DateUtil.java | 163 ++++++++++++++++++++++++++++++++
src/main/java/com/moral/service/HistoryFiveMinutelyService.java | 12 ++
5 files changed, 256 insertions(+), 0 deletions(-)
diff --git a/src/main/java/com/moral/mapper/HistoryFiveMinutelyMapper.java b/src/main/java/com/moral/mapper/HistoryFiveMinutelyMapper.java
new file mode 100644
index 0000000..3fa1913
--- /dev/null
+++ b/src/main/java/com/moral/mapper/HistoryFiveMinutelyMapper.java
@@ -0,0 +1,15 @@
+package com.moral.mapper;
+
+import org.apache.ibatis.annotations.Param;
+
+import java.util.List;
+import java.util.Map;
+
+public interface HistoryFiveMinutelyMapper {
+
+ List<Map<String, Object>> getFiveMinutesSensorData(Map<String, Object> map);
+
+ int insertHistoryFiveMinutely(@Param("list") List<Map<String, Object>> list, @Param("yearAndMonth") String yearAndMonth);
+
+ void createHistoryFiveMinutely(@Param("yearAndMonth") String yearAndMonth);
+}
diff --git a/src/main/java/com/moral/service/HistoryFiveMinutelyService.java b/src/main/java/com/moral/service/HistoryFiveMinutelyService.java
new file mode 100644
index 0000000..9d3e4a3
--- /dev/null
+++ b/src/main/java/com/moral/service/HistoryFiveMinutelyService.java
@@ -0,0 +1,12 @@
+package com.moral.service;
+
+import java.util.List;
+import java.util.Map;
+
+public interface HistoryFiveMinutelyService {
+ List<Map<String,Object>> getFiveMinutesSensorData(Map<String, Object> map);
+
+ int insertHistoryFiveMinutely(List<Map<String, Object>> list, String yearAndMonth);
+
+ void createHistoryFiveMinutelyTable(String yearAndMonth);
+}
diff --git a/src/main/java/com/moral/service/impl/HistoryFiveMinutelyServiceImpl.java b/src/main/java/com/moral/service/impl/HistoryFiveMinutelyServiceImpl.java
new file mode 100644
index 0000000..26993e8
--- /dev/null
+++ b/src/main/java/com/moral/service/impl/HistoryFiveMinutelyServiceImpl.java
@@ -0,0 +1,31 @@
+package com.moral.service.impl;
+
+import com.moral.mapper.HistoryFiveMinutelyMapper;
+import com.moral.service.HistoryFiveMinutelyService;
+import org.springframework.stereotype.Service;
+
+import javax.annotation.Resource;
+import java.util.List;
+import java.util.Map;
+
+@Service
+public class HistoryFiveMinutelyServiceImpl implements HistoryFiveMinutelyService{
+
+ @Resource
+ private HistoryFiveMinutelyMapper historyFiveMinutelyMapper;
+
+ @Override
+ public List<Map<String, Object>> getFiveMinutesSensorData(Map<String, Object> map) {
+ return historyFiveMinutelyMapper.getFiveMinutesSensorData(map);
+ }
+
+ @Override
+ public int insertHistoryFiveMinutely(List<Map<String, Object>> list, String yearAndMonth) {
+ return historyFiveMinutelyMapper.insertHistoryFiveMinutely(list,yearAndMonth);
+ }
+
+ @Override
+ public void createHistoryFiveMinutelyTable(String yearAndMonth) {
+ historyFiveMinutelyMapper.createHistoryFiveMinutely(yearAndMonth);
+ }
+}
diff --git a/src/main/java/com/moral/util/DateUtil.java b/src/main/java/com/moral/util/DateUtil.java
index 24b3a2a..4ed0b22 100644
--- a/src/main/java/com/moral/util/DateUtil.java
+++ b/src/main/java/com/moral/util/DateUtil.java
@@ -1,9 +1,172 @@
package com.moral.util;
import java.text.SimpleDateFormat;
+import java.util.Calendar;
import java.util.Date;
public class DateUtil {
+ /**
+ * ������������������������������2010-12-01
+ */
+ public static String FORMAT_SHORT = "yyyy-MM-dd";
+ /**
+ * ������������ ������2010-12-01 23:15:06
+ */
+ public static String FORMAT_LONG = "yyyy-MM-dd HH:mm:ss";
+ /**
+ * ������������������������������ ������yyyy-MM-dd HH:mm:ss.S
+ */
+ public static String FORMAT_FULL = "yyyy-MM-dd HH:mm:ss.S";
+ /**
+ * ������������ ������2010���12���01���
+ */
+ public static String FORMAT_SHORT_CN = "yyyy���MM���dd";
+ /**
+ * ������������ ������2010���12���01��� 23���15���06���
+ */
+ public static String FORMAT_LONG_CN = "yyyy���MM���dd��� HH���mm���ss���";
+ /**
+ * ������������������������������������
+ */
+ public static String FORMAT_FULL_CN = "yyyy���MM���dd��� HH���mm���ss���SSS������";
+
+
+ public static void main(String[] args) {
+ Date date = rollMinute(new Date(), -2);
+ System.out.println(date);
+
+ }
+
+
+
+ /**
+ * ������������������
+ */
+ public static String getTimeString() {
+ SimpleDateFormat df = new SimpleDateFormat(FORMAT_SHORT_CN);
+ Calendar calendar = Calendar.getInstance();
+ return df.format(calendar.getTime());
+ }
+
+ /**
+ * ������������������
+ * @param date ������
+ * @return
+ */
+ public static String getYear(Date date) {
+ SimpleDateFormat df = new SimpleDateFormat(FORMAT_FULL);
+ return df.format(date).substring(0, 4);
+ }
+
+ /**
+ * ������������������������
+ *
+ * @param date
+ * Date ������
+ * @return ������������
+ */
+ public static String getMonth(Date date) {
+ Calendar calendar = Calendar.getInstance();
+ calendar.setTime(date);
+ int Month = calendar.get(Calendar.MONTH);
+ Month++;
+ String str = String.valueOf(Month);
+ return str = str.length()==1?"0".concat(str):str;
+ }
+
+ /**
+ * ���������������������������
+ *
+ * @param date
+ * Date ������
+ * @return ������������
+ */
+ public static int getDay(Date date) {
+ Calendar calendar = Calendar.getInstance();
+ calendar.setTime(date);
+ return calendar.get(Calendar.DAY_OF_MONTH);
+ }
+
+ /**
+ * ���������������������������
+ *
+ * @param date
+ * ������
+ * @return ������������
+ */
+ public static int getHour(Date date) {
+ Calendar calendar = Calendar.getInstance();
+ calendar.setTime(date);
+ return calendar.get(Calendar.HOUR_OF_DAY);
+ }
+
+ /**
+ * ������������������������
+ *
+ * @param date
+ * ������
+ * @return ������������
+ */
+ public static String getMinute(Date date) {
+ Calendar calendar = Calendar.getInstance();
+ calendar.setTime(date);
+ int minute = calendar.get(Calendar.MINUTE);
+ String str = String.valueOf(minute);
+ return str = str.length()==1?"0".concat(str):str;
+ }
+
+ /**
+ * ������������
+ *
+ * @param date
+ * Date ������
+ * @return ������������
+ */
+ public static int getSecond(Date date) {
+ Calendar calendar = Calendar.getInstance();
+ calendar.setTime(date);
+ return calendar.get(Calendar.SECOND);
+ }
+
+ /**
+ * ������������������������
+ *
+ * @param date
+ * ������
+ * @return ���������
+ */
+ public static long getMillis(Date date) {
+ Calendar calendar = Calendar.getInstance();
+ calendar.setTime(date);
+ return calendar.getTimeInMillis();
+ }
+
+ /**
+ * ���������������������������������������������
+ *
+ * @param
+ *
+ * @return ������������������
+ */
+ public static Date rollMinute(Date d, int minute) {
+ return new Date(d.getTime() + minute * 60 * 1000);
+ }
+
+
+ /**
+ * ���/���?���
+ *
+ * @param d
+ * @param mon
+ * @return
+ */
+ public static Date rollMon(Date d, int mon) {
+ Calendar cal = Calendar.getInstance();
+ cal.setTime(d);
+ cal.add(Calendar.MONTH, mon);
+ return cal.getTime();
+ }
+
//������������i���������������
public static String getOldTime(int i){
Long time=System.currentTimeMillis();
diff --git a/src/main/resources/mapper/HistoryFiveMinutelyMapper.xml b/src/main/resources/mapper/HistoryFiveMinutelyMapper.xml
new file mode 100644
index 0000000..2dc2f89
--- /dev/null
+++ b/src/main/resources/mapper/HistoryFiveMinutelyMapper.xml
@@ -0,0 +1,35 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
+<mapper namespace="com.moral.mapper.HistoryFiveMinutelyMapper">
+ <select id="getFiveMinutesSensorData" resultType="java.util.LinkedHashMap">
+ select
+ h.mac
+ <foreach collection="sensorKeys" open="," separator="," item="sensorKey">
+ ROUND(AVG(json->'$.${sensorKey}[0]'),3) AS 'AVG${sensorKey}',
+ ROUND(MIN(json->'$.${sensorKey}[1]'),3) AS 'MIN${sensorKey}',
+ ROUND(MAX(json->'$.${sensorKey}[2]'),3) AS 'MAX${sensorKey}'
+ </foreach>
+ FROM
+ history_minutely_${yearAndMonth} h
+ where h.time<![CDATA[>=]]>#{start} and h.time <![CDATA[<=]]> #{end}
+ group by h.mac
+ </select>
+
+ <insert id="insertHistoryFiveMinutely">
+ insert into
+ history_five_minutely_${yearAndMonth}
+ values
+ <foreach collection="list" item="map" separator=",">
+ (#{map.mac},#{map.time},#{map.json})
+ </foreach>
+ </insert>
+
+ <update id="createHistoryFiveMinutely" parameterType="String">
+ CREATE TABLE IF NOT EXISTS `history_five_minutely_${yearAndMonth}` (
+ `mac` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
+ `time` datetime DEFAULT NULL,
+ `json` json DEFAULT NULL
+ ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
+ </update>
+
+</mapper>
\ No newline at end of file
--
Gitblit v1.8.0