Filter by date comparison in MongoDB

[Reynier Pérez]

I'm trying to filter a set of values in a collection (`messages`) by a date columns. I have two columns: 

    date: Wed September 18, 2013 

    mdate: 9/18/2013 6:19:40 PM

Using MongoVUE I tried this:

    {"date" : { $gte : new ISODate("2014-09-12 18:14:58") }}

But didn't work and I get this message:

> Syntax or parsing error

I need to build the proper OR to compare both columns, what is wrong on the query? What is the proper query?

[Wan Bachtiar]

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.

​