How to write a query to find the mongoDB documents whose time difference between two Date fields is larger than a certain value?

userToU5 0
Viewed 18

I have a mongoDB that contains documents like this:

enter image description here

The data types of start_local_datetime and last_update_local_datetime are both Date.

How can I find the documents whose difference between last_update_local_datetime and start_local_datetime is larger than 10 days?

I mean I want to query data like this:

start_local_datetime: 2019-08-23T10:17:42.000+00:00
terminate_local_datetime: 2019-09-19T10:17:42.000+00:00

Documents like this aren't something that I want.

start_local_datetime: 2019-08-23T10:17:42.000+00:00
terminate_local_datetime: 2019-08-25T10:17:42.000+00:00

Because terminate_local_datetime - start_local_datetime is smaller than 10 days.

mongodb
1 Answers

You can write an aggregation pipeline, using $dateDiff operator, like this:

db.collection.aggregate([
  {
    "$match": {
      $expr: {
        "$gt": [
          {
            "$dateDiff": {
              "startDate": "$start_local_datetime",
              "unit": "day",
              "endDate": "$terminate_local_datetime"
            }
          },
          10
        ]
      }
    }
  }
])

See it working here. However, this will only work in Mongo 5.0 or above, as the operator was added in that version. For other versions, this will work

db.collection.aggregate([
  {
    "$addFields": {
      "timeDifference": {
        "$divide": [
          {
            "$subtract": [ <-- Returns difference between two dates in milliseconds
              "$terminate_local_datetime",
              "$start_local_datetime"
            ]
          },
          86400000
        ]
      }
    }
  },
  {
    "$match": {
      $expr: {
        "$gt": [
          "$timeDifference",
          10
        ]
      }
    }
  },
  {
    "$project": {
      timeDifference: 0
    }
  }
])

Here, we calculate the time difference manually and then compare it, with 10. This is the playground link.

Charchit Kapoor0
Related