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
Share:
16,578
Rahul Saini
Author by

Rahul Saini

Updated on June 04, 2022

Comments

  • Rahul Saini
    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.