Mongodb query to Count days between two dates of each document

user_7252466 0
Viewed 3777

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'

aggregation-framework mongodb-query mongodb
3 Answers

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

user_72376130
user_64359080

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!

user_12628200
Related