MongoDB Query by Date in Java

Question

I`m trying to query mongodb for documents where "date" is betwen two dates. Sample data is:

{
        "_id" : ObjectId("4fad0af6709fbc1d481c3e05"),
        "ID" : NumberLong("200930746205085696"),
        "text" : "Forgot my charger...:(",
        "date" : ISODate("2012-06-14T10:49:57Z"),
        "sentiment" : "NEG"
}

My Java code is:

DBCursor cursor = null;
DB db = connect();

    Date startDate = new Date(System.currentTimeMillis() - (long)(numOfTimePeriods+1)*time);
    Date endDate = new Date(System.currentTimeMillis() - (long)numOfTimePeriods*time);
    DBObject query = new BasicDBObject();
    query.put("date", new BasicDBObject("$gt", startDate).append("$lte", endDate));

    cursor = db.getCollection("status").find(query);

but the cursor object has no results.

Query object looks like this:

{ "date" : { "$gt" : { "$date" : "2012-05-15T00:16:15.184Z"} , "$lte" : { "$date" : "2012-06-14T10:16:15.184Z"}}}

I suspect the problem is the date representation in DB. Any suggestions on this?

Solution

That $date representation is just the toString representation in the Java driver, of a Date. It uses the strict JSON/BSON representation, rather than the extended 10gen BSON where values can be objects represented like they are in the shell. You shouldn't try to query in the shell using the toString output like that, because it won't work for a lot of cases.

Comments

[hansvb]

No, I think the query was fine. I use a very similar one, too, and it works.

[jocelyn]

$and is implcit on a property if you append values ?

[hansvb]

mongodb.org/display/DOCS/… "You can also combine these operators to specify ranges" Not sure if this works in the general case (with other operators). If you have time, can you try to combine $gt with $nin or something?

[andjelko]

No, I tried $and operator, tried from cmd, too, but with no success.

[andjelko]

Code seems to work, I made a mistake in date range I queried. I`ll confirm it works when app finishes the proccessing and answer the question. Thanks for help!