Compare two date fields in MongoDB

You can not compare a field with the value of another field with the normal query matching. However, you can do this with the aggregation framework:

db.so.aggregate( [    { $match: …your normal other query… },    { $match: { $eq: [ '$modified', '$sync' ] } }] );

I put …your normal other query… in there as you can make that bit use the index. So if you want to do this for only documents where the name field is charles you can do:

db.so.ensureIndex( { name: 1 } );db.so.aggregate( [    { $match: { name: 'charles' } },    { $project: {         modified: 1,         sync: 1,        name: 1,        eq: { $cond: [ { $gt: [ '$modified', '$sync' ] }, 1, 0 ] }     } },    { $match: { eq: 1 } }] );

With the input:

{ "_id" : ObjectId("520276459bf0f0f3a6e4589c"), "modified" : 73845345, "sync" : 73234 }{ "_id" : ObjectId("5202764f9bf0f0f3a6e4589d"), "modified" : 4, "sync" : 4 }{ "_id" : ObjectId("5202765b9bf0f0f3a6e4589e"), "modified" : 4, "sync" : 4, "name" : "charles" }{ "_id" : ObjectId("5202765e9bf0f0f3a6e4589f"), "modified" : 4, "sync" : 45, "name" : "charles" }{ "_id" : ObjectId("520276949bf0f0f3a6e458a1"), "modified" : 46, "sync" : 45, "name" : "charles" }

This returns:

{    "result" : [        {            "_id" : ObjectId("520276949bf0f0f3a6e458a1"),            "modified" : 46,            "sync" : 45,            "name" : "charles",            "eq" : 1        }    ],    "ok" : 1}

If you want any more fields, you need to add them in the $project.

For MongoDB 3.6 and newer:

The $expr operator allows the use of aggregation expressions within the query language, thus you can do the following:

db.test.find({ "$expr": { "$gt": ["$modified", "$sync"] } })

or using aggregation framework with $match pipeline

db.test.aggregate([    { "$match": { "$expr": { "$gt": ["$modified", "$sync"] } } }])

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": [            { "$gt": ["$modified", "$sync"] },            "$$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:

Simply

db.collection.find({$where:"this.modified>this.sync"})

Example

Kobkrits-MacBook-Pro-2:~ kobkrit$ mongoMongoDB shell version: 3.2.3connecting to: test> db.time.insert({d1:new Date(), d2: new Date(new Date().getTime()+10000)})WriteResult({ "nInserted" : 1 })> db.time.find(){ "_id" : ObjectId("577a619493653ac93093883f"), "d1" : ISODate("2016-07-04T13:16:04.167Z"), "d2" : ISODate("2016-07-04T13:16:14.167Z") }> db.time.find({$where:"this.d1<this.d2"}){ "_id" : ObjectId("577a619493653ac93093883f"), "d1" : ISODate("2016-07-04T13:16:04.167Z"), "d2" : ISODate("2016-07-04T13:16:14.167Z") }> db.time.find({$where:"this.d1>this.d2"})> db.time.find({$where:"this.d1==this.d2"})>