如何在mongodb中减去两个日期时间 [英] How to subtract two date time in mongodb

问题描述

我使用了聚合函数.

db.checkins.aggregate([
       {$match: {checkinType: "Beacon",
               "associationIds.organizationId":"af39bc69-1938-4149",
               "checkinData.time": {"$gte": new Date("2018-01-18T18:30:00.000Z"), 
                                   "$lt": new Date("2018-01-19T18:30:00.000Z")}
                }
        },
       {"$sort":{"checkinData.time":-1}},
       {$group: {"_id":
                    {"orgId":"$asst.organizationId", "userId":"$asst.userId"},
                    "lastSeen":{"$first":"$checkinData.time"},
                   "firstSeen":{"$last":"$checkinData.time"},
               }
       }, 
      {"$project":{"_id":1,"lastSeen":1, "firstSeen":1, 
                  totalHourSpent:{$subtract: ["$lastSeen","$firstSeen"]}}}, 
  ])

当我执行此查询mongo时，以毫秒为单位返回 totalHourSpent ，如下所示.

{
  "_id" : {
        "orgId" : "af39bc69-1938-4149-b9f7-f101fd9baf73",
        "userId" : "34adb4a0-0012-11e7-bf32-cf79d6b423e9"
  },
 "lastSeen" : ISODate("2018-01-19T18:49:52.242+05:30"),
 "firstSeen" : ISODate("2018-01-19T10:08:21.026+05:30"),
 "totalHourSpent" : NumberLong("31291216")
},
{
  "_id" : {
       "orgId" : "af39bc69-1938-4149-b9f7-f101fd9baf73",
       "userId" : "679416b0-3f88-11e7-8d27-77235eb1ba9b"
   },
   "lastSeen" : ISODate("2018-01-19T20:51:30.946+05:30"),
   "firstSeen" : ISODate("2018-01-19T11:07:44.256+05:30"),
   "totalHourSpent" : NumberLong("35026690")
 },

如何以小时为单位计算总花费时间.提前致谢.

推荐答案

$ subtract为您提供持续时间(以毫秒为单位).因此我们需要将持续时间除以小时数3600000.

$subtract gives you the duration in millisecond. So we need to divide the duration with 3600000 for hour format.

返回的工厂可以通过除以3600000(1小时中的毫秒数)转换为小时:

The returned mills can be converted to hour by dividing by 3600000 (number of milliseconds in 1 hour):

totalHourSpent:{$divide : [{$subtract: ["$lastSeen","$firstSeen"]}, 3600000]}

即

35026690÷3600000=9.72963611111111 hours

这篇关于如何在mongodb中减去两个日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！