'MongoDB - How to get data between start date and end date

Here is the MongoDB document.

[
  {
    name: "a",
    "start_date": ISODate("2021-09-21T12:13:34.151+05:30"),
    "end_date": ISODate("2021-09-24T12:13:34.000+05:30"),

  },
  {
    name: "b",
    "start_date": ISODate("2021-09-21T12:13:34.151+05:30"),
    "end_date": ISODate("2021-09-22T12:13:34.000+05:30"),

  },
  {
    name: "c",
    "start_date": ISODate("2021-09-21T12:13:34.151+05:30"),
    "end_date": ISODate("2021-09-21T12:13:34.000+05:30"),

  }
]

I want to fetch all data based on the start date and end date.

I have tried with the following:

db.collection.find({
  start_date: {
    $gte: new Date()
  },
  end_date: {
    $lte: new Date()
  }
})

Expected output

If the current date is 2021-09-22, then the output will be:

[
  {
    name: "a",
    "start_date": ISODate("2021-09-21T12:13:34.151+05:30"),
    "end_date": ISODate("2021-09-24T12:13:34.000+05:30"),

  },
  {
    name: "b",
    "start_date": ISODate("2021-09-21T12:13:34.151+05:30"),
    "end_date": ISODate("2021-09-22T12:13:34.000+05:30"),

  },

]

If the current date is 2021-09-23, then the output will be:

[
  {
    name: "a",
    "start_date": ISODate("2021-09-21T12:13:34.151+05:30"),
    "end_date": ISODate("2021-09-24T12:13:34.000+05:30"),

  },

]
mongodbmongoosemongodb-query


Solution 1:[1]

Use $and for multiple expression.

Note: start_date less than ($lte) current date and end_date greater than ($gte) current date so that current date is within the date range.

db.collection.find({
  $and: [
    {
      start_date: {
        $lte: new Date()
      }
    },
    {
      end_date: {
        $gte: new Date()
      }
    }
  ]
})

Sample Mongo Playground 1

OR

db.collection.find({
  "$expr": {
    "$and": [
      {
        $lte: [
          "$start_date",
          new Date()
        ]
      },
      {
        $gte: [
          "$end_date",
          new Date()
        ]
      }
    ]
  }
})

Sample Mongo Playground 2

Solution 2:[2]

Kindly change the logic as mentioned below for the required output

db.collection.find({
  start_date: {
    $lte: new Date()
  },
  end_date: {
    $gte: new Date()
  }
})

Solution 3:[3]

    // With NodeJs
    Collection.find({
    $and: [
      {
        start_date: {
          $gte: new Date().toISOString(),
        },
      },
      {
        end_date: {
          $lte: new Date().toISOString(),
        },
      },
    ],
  });

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Yong Shun
Solution 2 Jaffar Javeed
Solution 3

Related Questions