Can't query date from MongoDB(Mlab) -- So simple but can't be solve

Question

After weeks of effort, I'm still having trouble querying the date field movie_datetime which has the structure below from Mlab using NodeJS.

"movie_datetime": {
        "$date": "2017-01-03T16:00:00.000Z"
},
"session_id": 31268

I tried the following

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

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

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

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

Appreciate any help.

Current nodeJS Codes :

**Tried variations of single quotes and doubles quotes and omitting .toISOString

var dateTime = '{"movie_datetime":{"$gte" : "'+new Date("2017-01-03T16:00:00.000Z").toISOString()+'"}}';

var dateTimeJson =JSON.parse(dateTimeVar);

db.mycollection.find(dateTimeJson);


Result for print dateTime 
{"movie_datetime":{"$gte" : "2017-01-03T16:00:00.000Z"}}

Result for print dateTimeJson 
{ movie_datetime: { '$gte': '2017-01-03T16:00:00.000Z' } }

Node version 6.6.0

Are you sure that you have inserted your document with `$date` as key? — Jyothi Babu Araja, Jan 09 '17 at 08:36
The key is movie_datetime while $date is auto generated by mongoDB when my scraper inserts documents. — Yang, Jan 09 '17 at 09:30
Image attached in post above. Basically it's the same document structure I've shown. — Yang, Jan 09 '17 at 09:42
Well, I think the dates stored in collections are of `string` data-type and not `date` type. So only string comparison will work. — dikesh, Jan 09 '17 at 10:14
What context is this? You say you're running this in node.js, so are the example queries above actually in your application logic? Or are you using the `mongo` shell or the JSON editor in mLab's UI? — pneumee, Jan 11 '17 at 19:21
@pneumee The query is written in the nodeJS app. One observation is that the $date field is created only in Mlab and not in Mongo Shell when an ISO date is inserted into a collection. — Yang, Jan 12 '17 at 02:59
@Yang The `$date` field is simply a JSON representation of a BSON date object - https://docs.mongodb.com/manual/reference/mongodb-extended-json/#date - mLab's UI uses a JSON editor, so all documents viewed & edited must follow strict JSON formatting. The `mongo` shell is JavaScript interpreter, so you can use expressions like `Date()` or `ISODate()` - https://docs.mongodb.com/manual/core/shell-types/#date - That all said, the screenshot of your schema shows the field name as `date`, but your `find()` operations are querying on `movie_datetime`, which doesn't seem to be a valid field name. — pneumee, Jan 12 '17 at 07:11
@pneumee The date field that i've shown was from another document. I've editted the screenshot above. I still can't figure out what is wrong. — Yang, Jan 12 '17 at 09:31
@Yang okay. In that case, something like `db.mycollection.find({ "movie_datetime" : {"$gte": new Date("2013-10-01T00:00:00.000Z")} })` should work in node. If not, seems like something else is at play. (collection name incorrect for example?) — pneumee, Jan 12 '17 at 17:12
@pneumee That's what I did, codes seen above. It's definitely not a collection naming issue. I've got it working in Mongo Shell but not in NodeJS. — Yang, Jan 13 '17 at 02:39
@Yang It looks like your current node logic is converting the date objects to strings for the query? If the `movie_datetime` values are stored as date objects, then you need to use date objects in your code to query against them. Mixing data types won't work. — pneumee, Jan 13 '17 at 06:25
@pneumee Do you have a sample solution? I've tried passing the ISODate() function along with the date into the db but it doesn't work either. I've tried all of the top answers from stackoverflow. — Yang, Jan 14 '17 at 07:18

score 4 · Answer 1 · answered Jul 17 '18 at 17:59

4

If you are using the mlab interface, it's actually like this:

{
    "createdAt": {
        "$gt": {
            "$date": "2017-11-08T20:05:45.866Z"
        }
    }
}

answered Jul 17 '18 at 17:59

Richard

51
2

QuasiFigures · Answer 2 · 2017-01-09T09:48:58.330

0

I think this should do it.

db.myCollection.find({
  'movie_datetime.$date': {
    '$gte': new Date('2013-10-01T00:00:00.000Z')
  } 
});

edit - The picture you posted and the JSON you provided are different. For the picture it would be -

db.myCollection.find({
  'date.$date': {
    '$gte': new Date('2013-10-01T00:00:00.000Z')
  } 
});

edited Jan 09 '17 at 09:48

answered Jan 09 '17 at 08:40

QuasiFigures

67
5

db.myCollection.find({ "movie_datetime.$date" : {"$gte" : "new Date(2017-01-03T16:00:00.000Z)" } }); The query above doesn't work. The syntax is exactly what I wrote in NodeJs. – Yang Jan 09 '17 at 09:26
The picture i've posted is another document(updated to the right one). This isn't a field naming issue. – Yang Jan 12 '17 at 09:46

score 0 · Answer 3 · edited Jul 21 '17 at 08:49

0

Check the docs

Dates are inserted in format given below

{"myDate": {"$date": "2010-07-20T23:23:50Z"}}

edited Jul 21 '17 at 08:49

Rahul Sharma

2,867
2
27
40

answered Jul 21 '17 at 06:18

tsukyonomi06

514
1
6
19

Can't query date from MongoDB(Mlab) -- So simple but can't be solve

3 Answers3