Mongodb find query with date diff on two keys-CodePudding

Let say I have a database date entity with two date fields

{
   createdAt: "2022-01-01",
   updatedAt: "2022-01-02"
}

Is there any way to get all the entities where the difference between createdAt and updatedAt is less than three days ?

CodePudding user response：

It's unfortunate the dates aren't BSON dates, but anyway, here's one way you could do it.

db.collection.find({
  "$expr": {
    "$lt": [
      {
        "$dateDiff": {
          "startDate": {"$toDate": "$createdAt"},
          "endDate": {"$toDate": "$updatedAt"},
          "unit": "day"
        }
      },
      3
    ]
  }
})

Try it on mongoplayground.net.

If "createdAt" and "updatedAt" are already BSON dates, then the query is a bit simpler.

db.collection.find({
  "$expr": {
    "$lt": [
      {
        "$dateDiff": {
          "startDate": "$createdAt",
          "endDate": "$updatedAt",
          "unit": "day"
        }
      },
      3
    ]
  }
})