Mongodb + Java Drivers. Search by date range

Question

This is my first shot at using Mongodb with the java drivers. I can query the database via command line using javascript and the Date() object, however, I am having trouble using the driver. Based on my query, can anybody see what the problem is? Thanks

            Date current = new Date();
            DBCollection coll = db.getCollection("messages");

            BasicDBObject query = new BasicDBObject("created_on", new BasicDBObject("$gte", new Date(current.getYear(), current.getMonth(), current.getDate())).
                    append("created_on", new BasicDBObject("$lt", new Date(current.getYear(), current.getMonth() - 1, current.getDate()))));

            System.out.println("Query: " + query);


            DBCursor cursor = coll.find(query);

Query: { "created_on" : { "$gte" : { "$date" : "2012-12-06T05:00:00.000Z"} , "created_on" : { "$lt" : { "$date" : "2012-11-06T05:00:00.000Z"}}}}

P.S. In case it is not obvious, I'm trying to find all of the records within the last month.

Solution 1

Seems like you are constructing the query wrong. Please try the below one:

BasicDBObject query = new BasicDBObject("created_on", //
                      new BasicDBObject("$gte", new DateTime().toDate()).append("$lt", new DateTime().toDate()));

Datetime object is a library which simplies date manipulation in java. You can check that out. http://joda-time.sourceforge.net/

Also morphia is a nice java object-document-mapper (ODM) framework for working with mongodb through java driver. It simplifies querying through java.

https://github.com/jmkgreen/morphia

Solution 2

Based on the query that was output, you are looking for a document with a field created_on that also has a child named created_on. I assume no such document exists. In other words, you query is not correctly formed.

Your query object should look like this:

BasicDBObject dateRange = new BasicDBObject ("$gte", new Date(current.getYear(), current.getMonth(), current.getDate());
dateRange.put("$lt", new Date(current.getYear(), current.getMonth() - 1, current.getDate());

BasicDBObject query = new BasicDBObject("created_on", dateRange);

Also, as a sidebar, you probably should avoid using the three-argument constructor of the java.util.Date class, as it is deprecated. When working with dates in the MongoDB Java driver, I typically use the java.util.Calendar class, and its getTime() method.

Solution 3

I have not used the Java driver for mongo before, but it seems that the query you have created is not correct.

Query: { "created_on" : { "$gte" : { "$date" : "2012-12-06T05:00:00.000Z"} , "created_on" : { "$lt" : { "$date" : "2012-11-06T05:00:00.000Z"}}}}

The query should in fact end up looking like:

Query: { "created_on" : {$gte: start, $lt: end}}

Where start and end are dates. It seems like the second time you refer to "created_on" is unnecessary and in fact might be breaking your query.

NOTE: I have not had the chance to test out this theory, but I am working from http://cookbook.mongodb.org/patterns/date_range/ which seems to be very relevant to the question at hand.

Solution 4

Jodatime lib is very userful, Please make use of DateTimeZone.UTC for timezone parameter of DateTime. Once you set timezone, you will get accurate results. Try this

    Calendar cal = Calendar.getInstance();
    //get current year,month & day using Calender
    int year=cal.get(Calendar.YEAR);
    int monthNumber=cal.get(Calendar.MONTH);
    int dateNumber=cal.get(Calendar.DAY_OF_MONTH);
    monthNumber+=1;


    BasicDBObject query = new BasicDBObject("dateCreated",new BasicDBObject("$gte", new DateTime(year, monthNumber, dateNumber, 0, 0,DateTimeZone.UTC).toDate()).append("$lte",new DateTime(year, monthNumber, dateNumber, 23, 59,DateTimeZone.UTC).toDate()));

    System.out.println("formed query: "+query);

    DBCursor cursor = collection.find(query);
    while(cursor.hasNext())
    {
        System.out.println("found doc in given time range: "+cursor.next().toString());
    }

View more solutions