java mongo 根据时间戳聚合查询某一天的数据

时间戳聚合查询某一天的数据

 {
   
    "_id": {
   "$oid": "5ff83089c3db080017fdc968"},
    "app_id": 96,
    "timestamp": 1610100873416
  },
  {
   
    "_id": {
   "$oid": "5ff8303ac3db080017fdc966"}, 
    "app_id": 96,
    "timestamp": 1610100794510
  }

聚合查询SQL：

比如我想查询的SQL是：

select  day ,count(*) from xxx where app_id =xxx group by app_id , day

在mongo中将时间戳转化为时间的SQL如下：

mongo将时间戳转为日期格式

db.xxx.aggregate(
   [
     {
   
       $project: {
   
          timestamp: 1,
          app_id: 1,
          day: {
   $dateToString: {
   format: "%Y-%m-%d %H:%M:%S:%L", date:{
   "$add":[new Date(0),"$timestamp"]}}},
          day8: {
   $dateToString: {
   format: "%Y-%m-%d %H:%M:%S:%L", date:{
   "$add":[new Date(0),"$timestamp",28800000]}}}
       }
     }
   ]
)

转换的结果如下：day8是多个8个小时的结果

  {
   
    "_id": {
   "$oid": "5fb12ea4c33c7400175b9cb5"},
    "app_id": 45,
    "day": "2020-10-23 10:02:12:461",
    "day8": "2020-10-23 18:02:12:461",
    "timestamp": 1603447332461
  },
  {
   
    "_id": {
   "$oid": "5fb12ea4c33c7400175b9cb6"},
    "app_id": 45,
    "day": "2020-10-23 10:02:12:503",
    "day8": "2020-10-23 18:02:12:503",
    "timestamp": 1603447332503
  }

match和project组合查询

db.xxxx.aggregate(
    {
   $match:
         {
     "app_id":96,
            "timestamp": {
   "$exists": true},
            "timestamp": {
    "$gte": 1610035200000,"$lte" :1610121600000}
         }
     },
     {
   
       $project: {
   
          timestamp: 1,
         	app_id: 1,
          day: {
   $dateToString: {
   format: "%Y-%m-%d %H:%M:%S:%L", date:{
   "$add":[new Date(0),"$timestamp"