mongodb: query for the time period between two date fields

Question

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?

Answer 1

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.

Answer 2

Best option is to use the $redact aggregation pipeline stage:

db.collection.aggregate([
    { "$redact": {
        "$cond": {
            "if": {
                "$gt": [
                    { "$subtract": [ "$lastUpdate", "$createdDate" ] },
                    1000 * 60 * 60 * 24
                ]
            },
            "then": "$$KEEP",
            "else": "$$PRUNE"
        }
    }}
])

So you are looking at the milliseconds value from the difference being greater than the milliseconds value for one day. The $subtract does the math for the difference, and when two dates are subtracted the difference in miliseconds is returned.

The $redact operator takes a logical expression as "if", and where that condition is true it takes the action in "then" which is to $$KEEP the document. Where it is false then the document is removed from results with $$PRUNE.

Note that since this is a logical condition and not a set value or a range of values, then an "index" is not used.

Since the operations in the aggregation pipeline are natively coded, this is the fastest execution of such a statement that you can get though.

The alternate is JavaScript evaluation with $where. This takes a JavaScript function expression that needs to similarly return a true or false value. In the shell you can shorthand like this:

db.collection.find(function() {
    return ( this.lastUpdate.valueOf() - this.createdDate.valueOf() )
       > ( 1000 * 60 * 60 * 24 );
})

Same thing, except that JavaScript evalution requires interpretation and will run much slower than the .aggregate() equivalent. By the same token, this type of expression cannot use an index to optimize performance.

For the best results, store the difference in the document. Then you can simply query directly on that property, and of course you can index it as well.