mongodb: query for the time period between two date fields

user_1148189 0
Viewed 14351

If I have documents in the following schema saved in my mongoDB:

{
  createdDate: Date,
  lastUpdate: Date
}

is it possible to query for documents where the period of time between creation and the last update is e.g. greater than one day?

aggregation-framework mongodb-query mongodb
3 Answers

Starting in Mongo 5, it's a perfect use case for the new $dateDiff aggregation operator:

// { created: ISODate("2021-12-05T13:20"), lastUpdate: ISODate("2021-12-06T05:00") }
// { created: ISODate("2021-12-04T09:20"), lastUpdate: ISODate("2021-12-05T18:00") }
db.collection.aggregate([
  { $match: {
    $expr: {
      $gt: [
        { $dateDiff: { startDate: "$created", endDate: "$lastUpdate", unit: "hour" } },
        24
      ]
    }
  }}
])
// { created: ISODate("2021-12-04T09:20"), lastUpdate: ISODate("2021-12-05T18:00") }

This computes the number of hours of difference between the created and lastUpdate dates and checks if it's more than 24 hours.

user_92971440
Related