Compare 2 dates in mongo find method

Question

I have mongo documents containing a last_active date and a created date. I would like to search for all documents where the day of last_active is not equal to the day of created, but I have no clue how to write the query.

In MySQL I would write it like that:

WHERE DATE_FORMAT(created, '%Y-%m-%d') != DATE_FORMAT(last_active, '%Y-%m-%d')

Answer 1

For MongoDB 3.6 and newer:

The $expr operator allows the use of aggregation expressions within the query language, thus you can leverage the use of $dateToString operator to transform the date field:

db.test.find({ 
    "$expr": { 
        "$ne": [ 
             { "$dateToString": { "format": "%Y-%m-%d", "date": "$created" } }, 
             { "$dateToString": { "format": "%Y-%m-%d", "date": "$last_active" } }
        ] 
    } 
})

or using aggregation framework with $match pipeline

db.test.aggregate([
    { "$match": { 
        "$expr": { 
            "$ne": [ 
                { "$dateToString": { "format": "%Y-%m-%d", "date": "$created" } }, 
                { "$dateToString": { "format": "%Y-%m-%d", "date": "$last_active" } }
            ] 
        } 
    } }
])

---

For MongoDB 3.0+:

You can also use the aggregation framework with the $redact pipeline operator that allows you to process the logical condition with the $cond operator and uses the special operations $$KEEP to "keep" the document where the logical condition is true or $$PRUNE to "remove" the document where the condition was false.

Consider running the following aggregate operation which demonstrates the above concept:

db.test.aggregate([
    {
        "$redact": {
            "$cond": [
                { 
                    "$ne": [ 
                        { "$dateToString": { "format": "%Y-%m-%d", "date": "$created" } }, 
                        { "$dateToString": { "format": "%Y-%m-%d", "date": "$last_active" } }
                    ] 
                },
                "$$KEEP",
                "$$PRUNE"
            ]
        }
    }
])

This operation is similar to having a $project pipeline that selects the fields in the collection and creates a new field that holds the result from the logical condition query and then a subsequent $match, except that $redact uses a single pipeline stage which is more efficient:

db.test.aggregate([
    {
        "$project": { 
            "created": 1, 
            "last_active": 1,
            "sameDay": { 
                "$cond": [ 
                    { 
                        "$eq": [ 
                            {"$substr" : ["$last_active",0, 10]}, 
                            {"$substr" : ["$created",0, 10]}
                        ] 
                    }, true, false 
                ]
            } 
        } 
    },
    { "$match": { "sameDay": false } }
])

0r

db.test.aggregate([
    {
        "$project": { 
            "created": 1, 
            "last_active": 1,
            "sameDay": { 
                "$cond": [ 
                    { 
                        "$eq": [ 
                            { "$dateToString": { "format": "%Y-%m-%d", "date": "$created" } }, 
                            { "$dateToString": { "format": "%Y-%m-%d", "date": "$last_active" } }
                        ] 
                    }, true, false 
                ]
            } 
        } 
    },
    { "$match": { "sameDay": false } }
])

---

Another approach would be to use the $where operator in your find() method but note that the query will be fairly slow since using $where alone requires a table scan and the database executes the JavaScript expression or function for each document in the collection, so combine with indexed queries if you can as query performance also improves when you express it using the standard MongoDB operators (e.g., $gt, $in):

db.test.find({ 
   "$where": function() { 
       return this.created.getDate() !== this.last_active.getDate() 
   } 
});

or more compact:

db.test.find({ "$where": "this.created.getDate() !== this.last_active.getDate()" });

---

With the input:

/* 0 */
{
    "_id" : 1,
    "created" : ISODate("2014-12-19T06:01:17.171Z"),
    "last_active" : ISODate("2014-12-21T15:38:13.842Z")
}

/* 1 */
{
    "_id" : 2,
    "created" : ISODate("2015-07-06T12:17:32.084Z"),
    "last_active" : ISODate("2015-07-06T18:07:08.145Z")
}

/* 2 */
{
    "_id" : 3,
    "created" : ISODate("2015-07-06T06:01:17.171Z"),
    "last_active" : ISODate("2015-07-07T10:04:30.921Z")
}

/* 3 */
{
    "_id" : 4,
    "created" : ISODate("2015-07-06T06:01:17.171Z"),
    "last_active" : ISODate("2015-07-06T09:47:44.186Z")
}

/* 4 */
{
    "_id" : 5,
    "created" : ISODate("2013-12-19T06:01:17.171Z"),
    "last_active" : ISODate("2014-01-20T13:21:37.427Z")
}

The aggregation returns:

/* 0 */
{
    "result" : [ 
        {
            "_id" : 1,
            "created" : ISODate("2014-12-19T06:01:17.171Z"),
            "last_active" : ISODate("2014-12-21T15:38:13.842Z"),
            "sameDay" : false
        }, 
        {
            "_id" : 3,
            "created" : ISODate("2015-07-06T06:01:17.171Z"),
            "last_active" : ISODate("2015-07-07T10:04:30.921Z"),
            "sameDay" : false
        }, 
        {
            "_id" : 5,
            "created" : ISODate("2013-12-19T06:01:17.171Z"),
            "last_active" : ISODate("2014-01-20T13:21:37.427Z"),
            "sameDay" : false
        }
    ],
    "ok" : 1
}