There are 4 records in my database.

{"_id":{"$oid":"6200b15e91f762376cd43ac3"},"startDate":{"$date":"2021-01-01T00:00:00.000Z"},"endDate":{"$date":"2021-01-28T18:29:59.059Z"},"__v":0}
{"_id":{"$oid":"6200b15e91f762376cd43ac4"},"startDate":{"$date":"2021-01-01T00:00:00.000Z"},"endDate":{"$date":"2021-01-23T18:29:59.059Z"},"__v":0}
{"_id":{"$oid":"6200b15e91f762376cd43ac5"},"startDate":{"$date":"2021-01-05T00:00:00.000Z"},"endDate":{"$date":"2021-01-28T18:29:59.059Z"},"__v":0}
{"_id":{"$oid":"6200b15e91f762376cd43ac6"},"startDate":{"$date":"2021-01-05T00:00:00.000Z"},"endDate":{"$date":"2021-01-25T18:29:59.059Z"},"__v":0}

My UI looks like this:

My mongodb query is as follows: (what I have tried)

data.find({
                    "$or": [
                      {"startDate": {"$gte": startDate}},
                      {"endDate": {"$lte": endDate}}
                    ]
                  });

When is search using the query above, I get only 1 record, That is: {"_id":{"$oid":"6200b15e91f762376cd43ac3"},"startDate":{"$date":"2021-01-01T00:00:00.000Z"},"endDate":{"$date":"2021-01-28T18:29:59.059Z"},"__v":0} instead of all 4.

While sending from UI, I convert it is: req.query:

dateStart: '2021-01-01',
dateEnd: '2021-01-28',

And then convert it to new Date() format:

let startDate = new Date(req.query.dateStart); 
let endDate = new Date(req.query.dateEnd);

I don't know what wrong I am going in the query. Need Help! Thank you!

CodePudding user response：

With $and seems to work as expected :

 db.collection.aggregate([
  {
    $match: {
      startDate: {
        $gte: {
       "$date": "2021-01-01T00:00:00.000Z"
       }
       },
      endDate: {
        $lte: {
       "$date": "2021-01-28T00:00:00.000Z"
       }
      }
     }
  }
])

playground

CodePudding user response：

Since you are expecting all 4 records to be returned, for the end date you should be querying using the time before the date 2021-01-28 ends which is 2021-01-28T23:59:59.999Z instead of when the date starts which is 2021-01-28T00:00:00.000Z.

Here's a resource on timestamps format.

So a query like this would work:

db.collection.find({
  startDate: {
    $gte: {
      "$date": "2021-01-01T00:00:00.000Z"
    }
  },
  endDate: {
    $lte: {
      "$date": "2021-01-28T23:59:59.999Z"
    }
  }
})