Hi Reynier,
What MongoDB version are you using ?
Based on your data samples, both date
and mdate
fields appear to be strings which means you will not be able to run a date comparison query against those fields.
However, MongoVue seems to interpret and display ISODate() type similar to your sample of mdate
field.
To check on mdate
field type in MongoVUE, you can switch to TreeView collection mode. You should be able to see the type of each fields in your document on the Type
column.
Check your mdate
field, if it shows as DateTime then you are able to run date comparison query on mdate
.
An example to find all records where mdate
is later than or on the 2014-09-12 18:14:58
:
db.collection.find(
{ "mdate" :
{ "$gte" : ISODate("2014-09-12T18:14:58Z") }
}
)
Note the “ISODate” format of the date in the query string. This is used because MongoDB stores time in UTC by default, in alignment to the official BSON specification.
However, if the mdate
field type is String, you have to convert the values to ISODate.
For example, this JavaScript snippet when executed in MongoDB Shell v3.0 will convert all records’ values of date
and mdate
in the collection from String to ISODate, and store them in new fields called newDate
and newMdate
:
var convertStringToDate = function(document){
/* Example string format to convert to ISODate
date: 'Wed September 18, 2013'
mdate: '9/18/2013 6:19:40 PM'
Assumes dates are in consistent string format that can be parsed with new Date().
You probably want to add some sanity checking / error handling for real data
*/
var newDate = new Date(document.date);
var newMDate = new Date(document.mdate);
/* Store the converted values into new fields */
db.collection.update(
{ _id: document._id},
{ $set:
{ newDate: newDate,
newMdate: newMDate }
}
);
}
/* Find all records in collection and run our function for each record.
Use with caution.
*/
db.collection.find({}, {date:1, mdate:1}).forEach(convertStringToDate);
I need to build the proper OR to compare both columns, what is wrong on the query? What is the proper query?
If both of the fields are in ISODate format, you are able to use $or
operator to compare both fields like below:
db.collection.find(
{$or : [
{ "newMdate" :
{ "$gte" : ISODate("2014-09-12T18:14:58Z") }
},
{ "newDate" :
{ "$gte" : ISODate("2014-09-01T00:00:00Z") }
}
]
}
)
Regards,
Wan.