How to subtract two date time in mongodb
16,578
$subtract gives you the duration in millisecond. So we need to divide the duration with 3600000 for hour format.
The returned mills can be converted to hour by dividing by 3600000 (number of milliseconds in 1 hour):
totalHourSpent:{$divide : [{$subtract: ["$lastSeen","$firstSeen"]}, 3600000]}
ie
35026690÷3600000=9.72963611111111 hours
Author by
Rahul Saini
Updated on June 04, 2022Comments
-
Rahul Saini about 2 years
I have used aggregate function.
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"]}}}, ])
When I performed this Query mongo return totalHourSpent in milisecond which is given below.
{ "_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") },
How to calculate totalHourSpent in hour. Thanks in advance.