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

• 前言
• 一、版本
• 二、CODE
• • 1.长整型时间戳
  • 2.日期格式

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

MongoDB shell version v5.0.2	
springboot 2.5.5

maven 依赖

   
       org.springframework.boot
       spring-boot-starter-data-mongodb
   

注意：

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

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

输出：

{
    "_id" : 1638957600000.0,
    "fisrtTime" : 1638957899180.0,
    "lastTime" : 1638957600000.0
}


{
    "_id" : 1638682800000.0,
    "fisrtTime" : 1638683094363.0,
    "lastTime" : 1638682800000.0
}


{
    "_id" : 1638572400000.0,
    "fisrtTime" : 1638572699180.0,
    "lastTime" : 1638572405637.0
}

按日期聚合数据，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 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("{ $month : "$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 result = mongoTemplate.aggregate(Aggregation.newAggregation(operations), "collection", JSONObject.class);
        List mappedResults = result.getMappedResults();
        if (CollectionUtils.isEmpty(mappedResults)){
            return;
        }
        log.info(JSONObject.toJSONString(mappedResults));

输出：

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


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


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