Mongodb query to Count days between two dates of each document

Question

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'

Answer 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

Answer 2

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