3

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?

Matthew Moisen
  • 16,701
  • 27
  • 128
  • 231

4 Answers4

9

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

Wizard
  • 4,341
  • 1
  • 15
  • 13
  • Wizard, my apologies, I am using an ETL tool called Talend and cannot use Date() or ISODate() in my queries for some reason. It was suggested I take a look at the $date operator. – Matthew Moisen Oct 07 '14 at 02:39
  • @Matthew Moisen, Perhaps you can try `{$date:}`, milliseconds = new Date("dateString").getTime(). – Wizard Oct 07 '14 at 02:47
  • Thanks for this tip @Wizard, its VERY IMPORTANT POINT to be considered. We should not also try to test the by taking the query generated using java code and print the same in the shell. It wont work. For shell use ISODate it works the best. – Kartik Narayana Maringanti Apr 22 '17 at 11:21
4

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.

Community
  • 1
  • 1
Salvador Dali
  • 214,103
  • 147
  • 703
  • 753
2

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

Ohad Malinovsky
  • 71
  • 2
  • 5
0

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.

steampowered
  • 11,809
  • 12
  • 78
  • 98