java

datetime

mongodb

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?

Solution 1

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());

Solution 2

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);
        }

Solution 3

Using mongo client 3.0

DateFormat format = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'", Locale.ENGLISH);
Bson filter = new Document("$gte", format.parse("2015-05-01T00:00:00Z")).append("$lt", format.parse("2015-05-02T00:00:00Z"));
long count = db.getCollection("colection").count(new Document("field",filter) );

Solution 4

Search Date with specific format following steps are that this also work in mongo 3.0

SimpleDateFormat simpleDateFormat = new SimpleDateFormat ("yyyy.MM.dd");

First convert your date in your specific format, you can use any format and parse your date with above format and then pass in query.

String date ="2014.01.02"; 
String data1 ="2014.01.10";
Date startDate = simpleDateFormat.parse(date);  
Date endDate = simpleDateFormat.parse(date1);
BasicDBObject query = new BasicDBObject("fieldName",
   new BasicDBObject("$gte",startDate).append("$lt",endDate ));

So base on your requirement you can search date by giving argument in BasicDBobject.

Solution 5

You can do

    import org.bson.conversions.Bson;
    import static com.mongodb.client.model.Filters.*;
    import java.text.SimpleDateFormat;

    DateFormat format = new SimpleDateFormat("yyyy-MM-dd", Locale.ENGLISH);
    Bson filter = Filters.and(gte("date_field", format.parse("2018-10-01")),
                            lt("date_field", format.parse("2019-10-01")))

Solution 6

I approach the query like this

new Document("datetime",Document.parse(
    "{$gte: ISODate('" + new SimpleDateFormat("yyyy-MM-dd").format(fromDate) + "'), 
     $lte: ISODate('" + new SimpleDateFormat("yyyy-MM-dd").format(endDate) + "')}"
))

Solution 7

If you want the exact answers to do one more thing when you applying the date range use the UTC time for the same.

On your local machine, you can use the below code to find the UTC time from your machine or server:

java.util.TimeZone.setDefault(java.util.TimeZone.getTimeZone("UTC"));