SpringBoot Mongdb 两种时间格式按时间聚合的操作

---

前言

最近在工作中遇到了大量数据的查询，需要按时间进行查询，学习了几种Mongdb 按时间聚合的操作，做总结如下。

---

一、版本

MongoDB shell version v5.0.2	
springboot 2.5.5

maven 依赖

   <dependency>
       <groupId>org.springframework.boot</groupId>
       <artifactId>spring-boot-starter-data-mongodb</artifactId>
   </dependency>

二、CODE

---

注意：

1. 不同的mongo gui 对命令的识别是有区别 尤其是 单、双引号
2. mongodb 存储的日期类型是0时区
3. 长整型需要用 NumberLong() 做包装
4. 本文中的数据都是模拟数据 并不完整或合理，也没有进行排序，显示结构仅供参考

---

1.长整型时间戳

按5分钟聚合数据，mongo 语法实现

db.getCollection("energy-iot-history").aggregate([
    {"$match": {"ts" : { "$gte" : 1638198000000}}},
    {"$group" : {
        "_id" : {"$subtract" : ["$ts",{ "$mod" : ["$ts", 5 * 60 * 1000]}]},
        "fisrtTime" : {"$first" : "$ts"},
        "lastTime" : {"$last" : "$ts"}
    }}
]);

spring boot mongoTemplate 实现，（未调试）基本格式没问题，请自行调试

		MatchOperation match = Aggregation.match(Criteria.where("ts").gte(1638198000000L));
        ProjectionOperation project = Aggregation.project()
                .andExpression("{$subtract: {'$ts', {$mod: {'$ts', 300000 }}}}").as("key")
                .and("ts").as("ts");
        GroupOperation group = Aggregation.group("key")
                .first("ts").as("firstTime")
                .first("bmsSoc").as("lastTime");

        List<AggregationOperation> operations = new ArrayList<>();
        operations.add(match);
        operations.add(project);
        operations.add(group);
        AggregationResults<JSONObject> aggregate = mongoTemplate.aggregate(Aggregation.newAggregation(operations), "collection", JSONObject.class);
        List<JSONObject> mappedResults = aggregate.getMappedResults();
        if (CollectionUtils.isEmpty(mappedResults)){
            return;
        }
        log.info(JSONObject.toJSONString(mappedResults));

输出：

/* 1 */
{
    "_id" : 1638957600000.0,
    "fisrtTime" : 1638957899180.0,
    "lastTime" : 1638957600000.0
}

/* 2 */
{
    "_id" : 1638682800000.0,
    "fisrtTime" : 1638683094363.0,
    "lastTime" : 1638682800000.0
}

/* 3 */
{
    "_id" : 1638572400000.0,
    "fisrtTime" : 1638572699180.0,
    "lastTime" : 1638572405637.0
}

2.日期格式

按日期聚合数据，mongo 语法实现

db.getCollection('point-detail').aggregate([
    {"$match": {"targetHour" : { "$gte" : new Date('2021-12-04 07:00:00+00:00')}}},
    {"$group" : {
        "_id" : {"year": {"$year" : "$targetHour"}, "month":{"$month" : "$targetHour"}, "day" : {"$dayOfMonth" : "$targetHour"}},
        "fisrtTime" : {"$first" : "$targetHour"},
        "lastTime" : {"$last" : "$targetHour"}
    }}
])

spring boot mongoTemplate 实现，（未调试）基本格式没问题，请自行调试

		List<AggregationOperation> operations = new ArrayList<>();
        MatchOperation match = Aggregation.match(Criteria.where("targetHour").gte(new Date()));
        ProjectionOperation project = Aggregation.project().andExpression("{$year : \"$targetHour\"}").as("year")
                .andExpression("{ $month : \"$targetHour\"}").as("month")
                .andExpression("{ $day : \"$targetHour\"}").as("day")
                .and("targetHour").as("targetHour");
        GroupOperation group = Aggregation.group("year", "month", "day")
                .first("targetHour").as("targetHour");
        operations.add(match);
        operations.add(project);
        operations.add(group);
        //查询结果
        AggregationResults<JSONObject> result = mongoTemplate.aggregate(Aggregation.newAggregation(operations), "collection", JSONObject.class);
        List<JSONObject> mappedResults = result.getMappedResults();
        if (CollectionUtils.isEmpty(mappedResults)){
            return;
        }
        log.info(JSONObject.toJSONString(mappedResults));

输出：

/* 1 */
{
    "_id" : {
        "year" : 2021,
        "month" : 12,
        "day" : 7
    },
    "fisrtTime" : ISODate("2021-12-07T00:00:00.000Z"),
    "lastTime" : ISODate("2021-12-07T23:45:00.000Z")
}

/* 2 */
{
    "_id" : {
        "year" : 2021,
        "month" : 12,
        "day" : 9
    },
    "fisrtTime" : ISODate("2021-12-09T00:00:00.000Z"),
    "lastTime" : ISODate("2021-12-09T23:45:00.000Z")
}

/* 3 */
{
    "_id" : {
        "year" : 2021,
        "month" : 12,
        "day" : 5
    },
    "fisrtTime" : ISODate("2021-12-05T00:00:00.000Z"),
    "lastTime" : ISODate("2021-12-05T01:00:00.000Z")
}