Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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!

like image 897
Jason Polites Avatar asked Nov 06 '13 18:11

Jason Polites


People also ask

How do I query a date field in MongoDB?

Use the Date() Method in a Query in MongoDB The following code adds a document with the field dateAdded set to the current date if no document with the _id equal to 1 exists in the products collection. Copy var myDateString = Date(); mongosh wraps objects of Date type with the ISODate helper.

How do dates work in MongoDB?

You can specify a particular date by passing an ISO-8601 date string with a year within the inclusive range 0 through 9999 to the new Date() constructor or the ISODate() function. These functions accept the following formats: new Date("<YYYY-mm-dd>") returns the ISODate with the specified date.

How does MongoDB compare ISO date?

Comparison Based on Date in MongoDB First, create a collection called 'data' using the document to further understand the concept. Use the find() function to show all the documents in a collection. The following is the date-based return query. Records with a creation date after 2018-05-19T11:10:23Z will be returned.

What date format does MongoDB use?

MongoDB date format dd/mm/yyyy The date field is used to convert a date into a string. Optional, the format field is used for date format specification. Optional, the timezone field is used for the timezone of the operation result.


9 Answers

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.

like image 60
zero323 Avatar answered Oct 05 '22 19:10

zero323


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") }})
like image 37
Steve HHH Avatar answered Oct 05 '22 19:10

Steve HHH


Try this:

{ "dt" : { "$gte" : ISODate("2013-10-01") } }
like image 39
Jabba Avatar answered Oct 05 '22 17:10

Jabba


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

like image 43
siddharthrc Avatar answered Oct 05 '22 17:10

siddharthrc


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.

like image 43
3vangelos Avatar answered Oct 05 '22 17:10

3vangelos


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.

like image 39
coder Avatar answered Oct 05 '22 17:10

coder


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

like image 43
lewma Avatar answered Oct 05 '22 19:10

lewma


Wrap it with new Date():

{ "dt" : { "$lt" : new Date("2012-01-01T15:00:00.000Z") } }
like image 42
TheEhsanSarshar Avatar answered Oct 05 '22 17:10

TheEhsanSarshar


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.

like image 45
A. Rabus Avatar answered Oct 05 '22 18:10

A. Rabus