I stored a value as a java.util.Date() in my collection, but when I query to get values between two specific dates, I end up getting values outside of the range. Here's my code:
to insert
BasicDBObject object = new BasicDBObject(); ... object.put("dateAdded", new java.util.Date()); collection.insert(object);
to query
BasicDBObject query = new BasicDBObject(); query.put("dateAdded", new BasicDBObject("$gte", fromDate)); query.put("dateAdded", new BasicDBObject("$lte", toDate)); collection.find(query).sort(new BasicDBObject("dateAdded", -1));
when I query between Wed Jul 27 16:54:49 EST 2011
and Wed Jul 27 16:54:49 EST 2011
(basically fromDate = toDate), I get objects with dates like Tue Jul 26 09:43:37 EST 2011
which should definitely not be possible. What am I missing here?
The following example Java code is used to query a MongoDB date field, which you see its value as, e.g., ISODate("2020-09-23T10:42:16.983Z") in the mongo shell. And, this date is stored in the MongoDB database as type Date. DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd H:m:s"); Date fromDate = dateFormat.
For example, if MongoDB stores July 7, 2015 with any time, I want to match that day only with today's date. In MySQL, I would do SELECT col1, col2 FROM table WHERE DATE(date_field) = DATE(NOW()); Notice how MySQL has a function to change the date_field to the date only during matching.
You can specify a particular date by passing an ISO-8601 date string with a year within the inclusive range 0 through 9999 to the new Date() constructor or the ISODate() function. These functions accept the following formats: new Date("<YYYY-mm-dd>") returns the ISODate with the specified date.
What you're doing is querying only with {$lte: toDate}
losing $gte
operator in the key overwrite. What you want is:
query.put("dateAdded", BasicDBObjectBuilder.start("$gte", fromDate).add("$lte", toDate).get());
If you are using MongoTemplate of Spring-Data Mongodb, you can do the same in following way:-
public List<Link> getLinksBetweenDate(Date startDate, Date endDate) { Query query = new Query().addCriteria(Criteria.where("updatedOn").gt(startDate).lte(endDate)); return mongoTemplate.find(query, Link.class); }
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With