CODEWITHSUNDEEP
mongodbmongodb-query
Matthew Moisen
Matthew Moisen

Reputation: 18279

MongoDB How to Query with the $date operator?

Edit - Context: I'm using the Talend ETL tool and using ISODate or Date or new Date in the query like the following fail with an error, so I need a work around:

{'dt' : ISODate('2014-01-01') }
{'dt' : Date('2014-01-01') }
{'dt' : new Date('2014-01-01') }

I cannot do so without the following error:

at com.mongodb.util.JSONParser.read(JSON.java:272)
at com.mongodb.util.JSONParser.parse(JSON.java:161)
at com.mongodb.util.JSONParser.parseObject(JSON.java:231)
at com.mongodb.util.JSONParser.parse(JSON.java:195)
at com.mongodb.util.JSONParser.parse(JSON.java:145)
at com.mongodb.util.JSON.parse(JSON.java:81)
at com.mongodb.util.JSON.parse(JSON.java:66)

presumably because the ETL tool calls:

com.mongodb.DBObject myQuery_tMongoDBInput_1 = (com.mongodb.DBObject) com.mongodb.util.JSON
                    .parse("{'dt': new Date('2000-01-01T08:00:00Z')}");

Given that I cannot use the new Date() in the query for the com.mongodb.util.JSON.parse() method, is there a work around?

I'm using MongoDB v2.6.3 and cannot get the $date operator to work.

db.testdate.insert( {dt:ISODate('2014-01-01')} )
db.testdate.find()

enter image description here

db.testdate.find( {dt : {$date : '2014-01-01T00:00:00Z'}} )

enter image description here

error: { "$err" : "Can't canonicalize query: BadValue unknown top level operator: $date", "code" : 17287 }

db.testdate.find(  {dt : {$gte : {$date : '2000-01-01T00:00:00Z'}}} )

enter image description here

I've seen other examples where the use of the $date operator worked, but cannot get it to do so on my machine.

Does anyone know why?

Upvotes: 3

Views: 34183

Answers (4)

steampowered
steampowered

Reputation: 12062

Using the $dateoperator and other features of MongoDB Extended JSON is now covered in detail in the manual here:

https://www.mongodb.com/docs/manual/reference/mongodb-extended-json/

For dates between years 1970 and 9999, inclusive:

Canonical {"$date": {"$numberLong": "<millis>"}}

Relaxed {"$date": "<ISO-8601 Date/Time Format>"}

For dates before year 1970 or after year 9999:

Canonical {"$date": {"$numberLong": "<millis>"}}

Relaxed <Same as Canonical>

There are many other operators, such as $oid covered as well.

Upvotes: 1

Ohad Malinovsky
Ohad Malinovsky

Reputation: 81

When using the $date operator you should pass it Long value indicating the "Epoch Unix Time Stamp"

like: {"time": {"$gte": {"$date": 1570654800000}}}

Note that only mongodump and mongoexport can interpret $date, this won't work from mongo shell

Upvotes: 2

Salvador Dali
Salvador Dali

Reputation: 222461

Based on what is written in documentation, { "$date": "<date>" } is used in a strict mode and in mongoshell you need to use new Date ( <date> )

Take a look how you should query by dates (basically using $gte, $gt, $lte, $lt).

Here are some relevant answers: one and two.

Upvotes: 4

Wizard
Wizard

Reputation: 4421

$date is prepared for tools mongoimport, mongoexport, etc. Mongo shell can't recognize it, you should use Date() or ISODate() instead.

Upvotes: 11

Related Questions