How to find Records between startDate and endDate - Nodejs, mongodb

80
July 23, 2022, at 9:10 PM

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!

Answer 1

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

Answer 2

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"
    }
  }
})
Rent Charter Buses Company
READ ALSO
Unable to rollback parent table record if join table update fails

Unable to rollback parent table record if join table update fails

I've a M:N(many-to-many) relationship between Invoice and Item entities and I'm using sequelize unmanaged transaction to create an invoice by adding items in it

55
Call webpack-ed function from JS file

Call webpack-ed function from JS file

I'm facing an issue, the goal I want to achieve is call a function from a webpack file

82
how do you create a folder and write to heroku's ephemeral storage?

how do you create a folder and write to heroku's ephemeral storage?

i'm using nodejs, and when my script uses fsmkdir nothing seems to happen

53
Is there an additional server configuration for api redirection using nodejs?

Is there an additional server configuration for api redirection using nodejs?

Im currently developing a website for out company that requires connection between two servers via APIThe problem is, on our laptop it redirection works fine but when I deploy it on our test server, it uses its own URL instead of the one we set on the nodejs...

94