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

Question

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!

Arapaima

Answer 1

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 the 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.

Florettaflorette

Yeah. I think I was thrown off by what is returned when you print out the Query object in Spring. The serialized form of the query is not necessarily a valid query that you can just copy/paste into a mongo shell, which in itself is kind of frustrating. The culprit is here: grepcode.com/file/repo1.maven.org/maven2/org.mongodb/… – Arapaima

When comparing a partial (no time) date, I had to switch from new Date('2016-03-09') to ISODate('2016-03-09'). The former would return dates in the past for a $gte query. – Aeschines

@MattMolnar Noted, and updated the answer with an attribution. Thanks. – Florettaflorette

Answer 2

From the MongoDB cookbook page comments:

"dt" : 
{
    "$gte" : ISODate("2014-07-02T00:00:00Z"), 
    "$lt" : ISODate("2014-07-03T00:00:00Z") 
}

This worked for me. In full context, the following command gets every record where the dt date field has a date on 2013-10-01 (YYYY-MM-DD) Zulu:

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

Saccharo

I got an "ISODate undefined" error. I couldn't solve this problem. – Antiperiodic

@BatuhanAkkaya In python with pymongo, datetime.datetime is equivalent to ISODate. – Austriahungary

@Austriahungary Not quite. The equivalent of ISODate("2014-07-02T00:00:00Z") is datetime.datetime.strptime("2014-07-02T00:00:00Z", "%Y-%m-%dT%H:%M:%SZ").isoformat(). – Sigfrid

Answer 3

Try this:

{ "dt" : { "$gte" : ISODate("2013-10-01") } }

Confirmand

Answer 4

I am using robomongo as the mongodb client gui and the below worked for me

db.collectionName.find({"columnWithDateTime" : {
$lt:new ISODate("2016-02-28T00:00:00.000Z")}})

On the app side I am using nodejs based driver mongodb(v1.4.3),the application uses datepicker in the ui which gives date like YYYY-mm-dd, this is then appended with default time like 00:00:00 and then given to the new Date() constructor and then supplied to the mongodb criteria object,I think the driver converts the date to ISO date and the query then works and gives desired output, however the same new Date() constructor does not work or show same output on robo mongo,for the same criteria,which is weird,since I used robomongo to cross check my criteria objects.

Whereas the default cli mongoshell works well with both ISODate and new Date()

Haifa

Thank you for the tip, Robomongo is way better than Mongo Compass – Limbate

Answer 5

In json strict mode, you'll have to keep the order:

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

Only thing which worked to define my search queries on mlab.com.

Eddie

Error running query. Reason: (invalid_operator) Invalid operator: $date – Prisca

Answer 6

this is my document

"_id" : ObjectId("590173023c488e9a48e903d6"),
    "updatedAt" : ISODate("2017-04-27T04:26:42.709Z"),
    "createdAt" : ISODate("2017-04-27T04:26:42.709Z"),
    "flightId" : "590170f97cb84116075e2680",

 "_id" : ObjectId("590173023c488e9a48e903d6"),
        "updatedAt" : ISODate("2017-04-28T03:26:42.609Z"),
        "createdAt" : ISODate("2017-04-28T03:26:42.609Z"),
        "flightId" : "590170f97cb84116075e2680",

now i want to find every 27th date document.so i used this....

 > db.users.find({createdAt:{"$gte":ISODate("2017-04-27T00:00:00Z"),"$lt":ISODate("2017-04-28T00:00:00Z") }}).count()

result:1

this worked for me.

Lenin

Answer 7

Wrap it with new Date():

{ "dt" : { "$lt" : new Date("2012-01-01T15:00:00.000Z") } }

Halvorsen

Answer 8

Old question, but still first google hit, so i post it here so i find it again more easily...

Using Mongo 4.2 and an aggregate():

db.collection.aggregate(
    [
     { $match: { "end_time": { "$gt": ISODate("2020-01-01T00:00:00.000Z")  } } },
     { $project: {
          "end_day": { $dateFromParts: { 'year' : {$year:"$end_time"}, 'month' : {$month:"$end_time"}, 'day': {$dayOfMonth:"$end_time"}, 'hour' : 0  } }
     }}, 
     {$group:{
        _id:   "$end_day",
        "count":{$sum:1},
    }}
   ]
)

This one give you the groupby variable as a date, sometimes better to hande as the components itself.

Stomachic

Answer 9

In the MongoDB shell:

db.getCollection('sensorevents').find({from:{$gt: new ISODate('2015-08-30 16:50:24.481Z')}})

In my nodeJS code ( using Mongoose )

    SensorEvent.Model.find( {
        from: { $gt: new Date( SensorEventListener.lastSeenSensorFrom ) }
    } )

I am querying my sensor events collection to return values where the 'from' field is greater than the given date

Selry

Welcome to Stack Overflow! Please just post the answer to the question, and not extra stuff like "This is my first post". Think of Stack Overflow as Wikipedia, not a message board. – Arlinda

Answer 10

This worked for me while searching for value less than or equal than now:

db.collectionName.find({ "dt": { "$lte" : new Date() + "" } });

Hysterical

works in compass, but not in Atlas. – Estuarine

Answer 11

Although I tested this against AWS DocumentDB (which is supposed to be Mongo 4 compatible), this worked fine for me without any use of Dateor ISODate:

db.collection_name.find({"an_iso_date_field": {$lte: "2021-02-04T03:42:00Z"}})

Talebearer