'Mongodb query to Count days between two dates of each document

Sample Docs :

{
    "_id" : ObjectId("5e7331614dd99d1a30143a58"),
    "status" : "rejected",
    "from" : ISODate("2020-03-17T08:46:02.552Z"),
    "to" : ISODate("2020-03-18T08:46:07.124Z")
},
{
    "_id" : ObjectId("5e7331614dd99d1a30143a58"),
    "status" : "rejected",
    "from" : ISODate("2020-03-02T08:08:32.819Z"),
    "to" : ISODate("2020-03-05T08:08:37.125Z"),
}

I am new to mongodb, I want to count the number of days between dates (to and from fields) of each document where status is equal to 'rejected'

mongodbmongodb-queryaggregation-framework


Solution 1:[1]

You can try below query :

db.collection.aggregate([
  /** Filter out docs */
  { $match: { status: "rejected" } },
  /** subtract two dates gets timestamp & divide to convert to days & round value */
  {
    $addFields: {
      daysCount: {
        $round: { $divide: [{ $subtract: ["$to", "$from"] }, 86400000] }
      }
    }
  }
]);

Test : MongoDB-Playground

Solution 2:[2]

Subtract dates using $subtract operator ans then convert (use $divide) result in milliseconds to days: 

db.mycollection.aggregate([
      {
        "$project": {            
          "date_diff": { "$subtract": ["$to", "$from"] }
        }
      },
      {
        "$project": {             
          "days": { "$divide": ["$date_diff", 1000 * 60 * 60 * 24] }
        }
      }
    ])

Here there's two $project phases in pipeline just for easy visualization, but it could to be merged at one pipeline phase.

Additionally you may want to convert the number of days from float to integers. It's up to you!

Solution 3:[3]

If you are using MongoDB version 5.0, you can use the new $dateDiff operator for this.

Docs Link: https://docs.mongodb.com/manual/reference/operator/aggregation/dateDiff/#mongodb-expression-exp.-dateDiff

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 whoami - fakeFaceTrueSoul
Solution 2
Solution 3 Hisham Mubarak

Related Questions