Date query with ISODate in mongodb doesn't seem to work

Jason Polites picture Jason Polites · Nov 6, 2013 · Viewed 409k times · Source

I don't seem to be able to get even the most basic date query to work in MongoDB. With a document that looks something like this:

{
    "_id" : "foobar/201310",
    "ap" : "foobar",
    "dt" : ISODate("2013-10-01T00:00:00.000Z"),
    "tl" : 375439
}

And a query that looks like this:

{ 
    "dt" : { 
        "$gte" : { 
            "$date" : "2013-10-01T00:00:00.000Z"
        }
    }
}

I get 0 results from executing:

db.mycollection.find({
  "dt" : { "$gte" : { "$date" : "2013-10-01T00:00:00.000Z"}}
})

Any idea why this doesn't work?

For reference, this query is being produced by Spring's MongoTemplate so I don't have direct control over the query that is ultimately sent to MongoDB.

(P.S.)

> db.version()
2.4.7

Thanks!

Answer

zero323 picture zero323 · Nov 6, 2013

Although $date is a part of MongoDB Extended JSON and that's what you get as default with mongoexport I don't think you can really use it as a part of the query.

If try exact search with $date like below:

db.foo.find({dt: {"$date": "2012-01-01T15:00:00.000Z"}})

you'll get error:

error: { "$err" : "invalid operator: $date", "code" : 10068 }

Try this:

db.mycollection.find({
    "dt" : {"$gte": new Date("2013-10-01T00:00:00.000Z")}
})

or (following comments by @user3805045):

db.mycollection.find({
    "dt" : {"$gte": ISODate("2013-10-01T00:00:00.000Z")}
})

ISODate may be also required to compare dates without time (noted by @MattMolnar).

According to Data Types in the mongo Shell both should be equivalent:

The mongo shell provides various methods to return the date, either as a string or as a Date object:

  • Date() method which returns the current date as a string.
  • new Date() constructor which returns a Date object using the ISODate() wrapper.
  • ISODate() constructor which returns a Date object using the ISODate() wrapper.

and using ISODate should still return a Date object.

{"$date": "ISO-8601 string"} can be used when strict JSON representation is required. One possible example is Hadoop connector.