How to find the hours difference between two dates in mongodb

Question

I have the documents in following structure saved in my mongodb.

UserLogs

{
"_id":"111",
"studentID" : "1",
"loginTime" : "2019-05-01 09:40:00",
"logoutTime" : "2019-05-01 19:40:00"
},
{
"_id":"222",
"studentID" : "1",
"loginTime" : "2019-05-02 09:40:00",
"logoutTime" : "2019-05-02 20:40:00"
},
{
"_id":"333",
"studentID" : "2",
"loginTime" : "2019-05-02 09:40:00",
"logoutTime" : "2019-05-02 20:40:00"
}

is it possible to query for documents where the period of time between loginTime and logoutTime.eg: grater than 20 hrs

mongodb version = 3.4

Answer 1

You can use below aggregation

db.collection.aggregate([
  { "$project": {
    "difference": {
      "$divide": [
        { "$subtract": ["$logoutTime", "$loginTime"] },
        60 * 1000 * 60
      ]
    }
  }},
  { "$group": {
    "_id": "$studentID",
    "totalDifference": { "$sum": "$difference" }
  }},
  { "$match": { "totalDifference": { "$gte": 20 }}}
])

You have to just $subtract loginTime from logoutTime and it will give you the subtracted timestamp and then just $divide it with 3600000 to get the time in hours.

MongoPlayground

Answer 2

Since mongoDB version 5.0 you can simply use $dateDiff:

db.collection.aggregate([
  {$set: {
      hoursDiff: {
        $dateDiff: {
          startDate: {$toDate: "$loginTime"},
          endDate: {$toDate: "$logoutTime"},
          unit: "hour"
        }
      }
    }
  },
  {$match: {hoursDiff: {$gte: 20}}}
])

See how it works on the playground example