0
242
VistasHow to find Records between startDate and endDate - Nodejs, mongodb
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!
2 Respuestas
Responde la pregunta0
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"
}
}
}
}
])
0
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"
}
}
})