Compare two date fields in MongoDB

Question

In my collection, each document has 2 dates, modified and synchronized. I would like to find those that change> sync, or sync does not exist.

I tried

{'modified': { $gt : 'sync' }} 

but this does not show what I expected. Any ideas?

thanks

Answer 1

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

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

I put ... your usual other query ... where you can make this bit using an index. Therefore, if you want to do this only for documents, where the name charles field 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 } } ] ); 

When entering:

 { "_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 more fields, you need to add them to $project .

Answer 2

Just

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

Example

 Kobkrits-MacBook-Pro-2:~ kobkrit$ mongo MongoDB shell version: 3.2.3 connecting 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"}) > 

Answer 3

Use Javascript, use foreach and convert Date To toDateString ()

 db.ledgers.find({}).forEach(function(item){ if(item.fromdate.toDateString() == item.todate.toDateString()) { printjson(item) } }) 

Answer 4

For MongoDB 3.6 and later:

The $expr operator allows you to use aggregation expressions in the query language, so you can do the following:

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

or using an aggregation structure with $match pipeline

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

---

For MongoDB 3.0 +:

You can also use the aggregation structure using the $redact operator, which allows you to process the logical condition with the $cond operator and uses special $$KEEP operations to “save” the document, where the logical condition is true or $$PRUNE to “delete” the document. in which the condition was false.

Consider the following aggregation operation, which demonstrates the above concept:

 db.test.aggregate([ { "$redact": { "$cond": [ { "$gt": ["$modified", "$sync"] }, "$$KEEP", "$$PRUNE" ] } } ]) 

This operation is similar to the $project pipeline, which selects the fields in the collection and creates a new field that contains the result of querying the logical condition and then the next $match , except that $redact uses the more efficient one stage of the pipeline:

Answer 5

Your query is currently trying to return all results, so the modified field is larger than the word 'sync' . Try to get rid of the quotes around sync and see if this fixes anything. Otherwise, I did a little research and found this question . What you are trying to do may not be possible in a single request, but you can manage your data as soon as you pull everything out of the database.

Answer 6

To fix this problem without aggregation, change your request to this: {'modified': { $gt : ISODate(this.sync) }}