MongoDB/Mongoose querying at a specific date?
node.jsMongodbExpressMongooseOdmnode.js Problem Overview
Is it possible to query for a specific date ?
I found in the mongo Cookbook that we can do it for a range Querying for a Date Range Like that :
db.posts.find({"created_on": {"$gte": start, "$lt": end}})
But is it possible for a specific date ? This doesn't work :
db.posts.find({"created_on": new Date(2012, 7, 14) })
node.js Solutions
Solution 1 - node.js
That should work if the dates you saved in the DB are without time (just year, month, day).
Chances are that the dates you saved were new Date()
, which includes the time components. To query those times you need to create a date range that includes all moments in a day.
db.posts.find({ //query today up to tonight
created_on: {
$gte: new Date(2012, 7, 14),
$lt: new Date(2012, 7, 15)
}
})
Solution 2 - node.js
date-fns instead
...5+ years later, I strongly suggest usingimport endOfDayfrom 'date-fns/endOfDay'
import startOfDay from 'date-fns/startOfDay'
MyModel.find({
createdAt: {
$gte: startOfDay(new Date()),
$lte: endOfDay(new Date())
}
})
Moment.js
For those of us usingconst moment = require('moment')
const today = moment().startOf('day')
MyModel.find({
createdAt: {
$gte: today.toDate(),
$lte: moment(today).endOf('day').toDate()
}
})
Important: all moments are mutable!
tomorrow = today.add(1, 'days')
does not work since it also mutates today
. Calling moment(today)
solves that problem by implicitly cloning today
.
Solution 3 - node.js
Yeah, Date object complects date and time, so comparing it with just date value does not work.
You can simply use the $where operator to express more complex condition with Javascript boolean expression :)
db.posts.find({ '$where': 'this.created_on.toJSON().slice(0, 10) == "2012-07-14"' })
created_on
is the datetime field and 2012-07-14
is the specified date.
Date should be exactly in YYYY-MM-DD format.
Note: Use $where
sparingly, it has performance implications.
Solution 4 - node.js
Have you tried:
db.posts.find({"created_on": {"$gte": new Date(2012, 7, 14), "$lt": new Date(2012, 7, 15)}})
The problem you're going to run into is that dates are stored as timestamps in Mongo. So, to match a date you're asking it to match a timestamp. In your case I think you're trying to match a day (ie. from 00:00 to 23:59 on a specific date). If your dates are stored without times then you should be okay. Otherwise, try specifying your date as a range of time on the same day (ie. start=00:00, end=23:59) if gte doesn't work.
Solution 5 - node.js
You can use following approach for API method to get results from specific day:
# [HTTP GET]
getMeals: (req, res) ->
options = {}
# eg. api/v1/meals?date=Tue+Jan+13+2015+00%3A00%3A00+GMT%2B0100+(CET)
if req.query.date?
date = new Date req.query.date
date.setHours 0, 0, 0, 0
endDate = new Date date
endDate.setHours 23, 59, 59, 59
options.date =
$lt: endDate
$gte: date
Meal.find options, (err, meals) ->
if err or not meals
handleError err, meals, res
else
res.json createJSON meals, null, 'meals'
Solution 6 - node.js
i do it in this method and works fine
public async getDatabaseorderbyDate(req: Request, res: Response) {
const { dateQuery }: any = req.query
const date = new Date(dateQuery)
console.log(date)
const today = date.toLocaleDateString(`fr-CA`).split('/').join('-')
console.log(today)
const creationDate = {
"creationDate": {
'$gte': `${today}T00:00:00.000Z`,
'$lt': `${today}T23:59:59.999Z`
}
};
`
``
Solution 7 - node.js
Problem I came into was filtering date in backend, when setting date to 0 hour, 0 minute, 0 second, 0 milisecond in node server it does in ISO time so current date 0 hour, 0 minute, 0 second, 0 milisecond of client may vary i.e. as a result which may gives a day after or before due to conversion of ISO time to local timezone
I fixed those by sending local time from client to server
// If client is from Asia/Kathmandu timezone it will zero time in that zone.
// Note ISODate time with zero time is not equal to above mention
const timeFromClient = new Date(new Date().setHours(0,0,0,0)).getTime()
And used this time to filter the documents by using this query
const getDateQuery = (filterBy, time) => {
const today = new Date(time);
const tomorrow = new Date(today.getDate() + 1);
switch(filterBy) {
case 'past':
return {
$exists: true,
$lt: today,
};
case 'present':
return {
$exists: true,
$gte: today,
$lt: tomorrow
};
case 'future':
return {
$exists: true,
$gte: tomorrow
};
default:
return {
$exists: true
};
};
};
const users = await UserModel.find({
expiryDate: getDateQuery('past', timeFromClient)
})
Solution 8 - node.js
We had an issue relating to duplicated data in our database, with a date field having multiple values where we were meant to have 1. I thought I'd add the way we resolved the issue for reference.
We have a collection called "data" with a numeric "value" field and a date "date" field. We had a process which we thought was idempotent, but ended up adding 2 x values per day on second run:
{ "_id" : "1", "type":"x", "value":1.23, date : ISODate("2013-05-21T08:00:00Z")}
{ "_id" : "2", "type":"x", "value":1.23, date : ISODate("2013-05-21T17:00:00Z")}
We only need 1 of the 2 records, so had to resort the javascript to clean up the db. Our initial approach was going to be to iterate through the results and remove any field with a time of between 6am and 11am (all duplicates were in the morning), but during implementation, made a change. Here's the script used to fix it:
var data = db.data.find({"type" : "x"})
var found = [];
while (data.hasNext()){
var datum = data.next();
var rdate = datum.date;
// instead of the next set of conditions, we could have just used rdate.getHour() and checked if it was in the morning, but this approach was slightly better...
if (typeof found[rdate.getDate()+"-"+rdate.getMonth() + "-" + rdate.getFullYear()] !== "undefined") {
if (datum.value != found[rdate.getDate()+"-"+rdate.getMonth() + "-" + rdate.getFullYear()]) {
print("DISCREPENCY!!!: " + datum._id + " for date " + datum.date);
}
else {
print("Removing " + datum._id);
db.data.remove({ "_id": datum._id});
}
}
else {
found[rdate.getDate()+"-"+rdate.getMonth() + "-" + rdate.getFullYear()] = datum.value;
}
}
and then ran it with mongo thedatabase fixer_script.js
Solution 9 - node.js
Seemed like none of the answers worked for me. Although someone mentioned a little hint, I managed to make it work with this code below.
let endDate = startingDate
endDate = endDate + 'T23:59:59';
Model.find({dateCreated: {$gte: startingDate, $lte: endDate}})
startingDate
will be the specific date you want to query with.
I preferred this solution to avoid installing moment
and just to pass the startingDate
like "2021-04-01"
in postman.
Solution 10 - node.js
Well a very simple solution to this is given below
const start = new Date(2020-04-01);
start.setHours(0, 0, 0, 0);
const end = new Date(2021-04-01);
end.setHours(23, 59, 59, 999);
queryFilter.created_at={
$gte:start,
$lte:end
}
YourModel.find(queryFilter)
So, the above code simply finds the records from the given start date to the given end date.