Possible to compare date strings in mongodb?

Question

I have a collection that contains documents with a date attribute like so:

{
   title: "whatever",
   created: ISODate("2013-05-27T03:36:50Z")
}

I would like to select records that were created on a certain day. I was hoping I could use toDateString() and compare the values but when I do a search like the one below:

db.myCollection.find({ 'created.toDateString()': new Date('05/27/2013').toDateString() }).pretty()

But this does not work. Is there any way to do what I am attempting above?

Solution

If you want to select records then use a date range:

db.collection.find({
    created: { "$gte": new Date("2013-05-27"), "$lt": new Date("2013-05-28") }
})

And that selects all the contained hours, minutes etc, falling between the two dates.

So you should be trying to use the date values and not coerce into strings.

If you want this for doing aggregation or otherwise need the results in a day only format then do this using $project and the date operators:

db.collection.aggregate([
    // Still match on the normal date forms, this time whole month
    { "$match": {
        created: { 
           "$gte": new Date("2013-05-01"), 
           "$lt": new Date("2013-05-31") 
        }
    }},

    // Project the date
    { "$project": {
        "date": {
            "year"  : { "$year"  : "$created" },
            "month" : { "$month" : "$created" },
            "day":  : { "$dayOfMonth": "$created" }
        },
        "title": 1
    }},

    // Group on day and title
    { "$group": {
        "_id": {
            "date"  : "$date",
            "title" : "$title"
        },
        "count": { "$sum": 1 }
    }},

    // Sort by date
    { "$sort": { 
        "_id.date.year": 1,
        "_id.date.month": 1,
        "_id.date.day": 1,
    }},

    // Project nicer dates and document
    { "$project": {
        "_id": 0,
        "date": { "$concat": [
            { "$substr": [ "$_id.date.year", 0, 4 ] },
            "-",
            { "$substr": [ "$_id.date.month", 0, 2 ] },
            "-",
            { "$substr": [ "$_id.date.day", 0, 2 ] }
        ]},
        "title": "$_id.title",
        "count": 1
    }}
])

Comments

[Abe Miessler]

Thanks for the info Neil. Can I ask why I "...should be trying to use the date values and not coerce into strings"?

[Neil Lunn]

@AbeMiessler it's generally not a good idea because there is a cost involved in the coercion. What you were attempting with find would not work, unless using $where, and that would force a table scan, as well as slowing down for executed JavaScript. Finally it is not necessary as shown in both examples. The only valid case is for a final projection, but there is no reason why you cannot do that part in code. Always use dates.

[gezquinndesign]

Thanks for this! There's a small typo FYI - two colons after the day field in the date projection.