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

Ashh · Accepted Answer · 2019-05-12T18:04:13.620

14

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

edited May 12 '19 at 18:04

answered May 12 '19 at 17:39

Ashh

44,693
14
105
132

Can u please check this [LINK](https://stackoverflow.com/questions/56102636/how-to-join-the-two-table-it-is-in-two-different-db-in-mongodb) – Sagu K May 13 '19 at 03:44

score 1 · Answer 2 · answered Jul 26 '22 at 14:43

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

How to find the hours difference between two dates in mongodb

2 Answers2

Linked

Related