Calculate difference by date in year, month, day

Question

I have the following query:

db.getCollection('user').aggregate([ {$unwind: "$education"}, {$project: { duration: {"$divide":[{$subtract: ['$education.to', '$education.from'] }, 1000 * 60 * 60 * 24 * 365]} }}, {$group: { _id: '$_id', "duration": {$sum: '$duration'} }}] ]) 

Above query result:

 { "_id" : ObjectId("59fabb20d7905ef056f55ac1"), "duration" : 2.34794520547945 } /* 2 */ { "_id" : ObjectId("59fab630203f02f035301fc3"), "duration" : 2.51232876712329 } 

But what I want to do is get its duration in the format year + month + day , something like: 2 y, 3 m, 20 d . Another point, if the course goes in the to field, is null, and the other field isGoingOn: true , so here I have to calculate the duration using the current date instead of the to field. And the user has an array of course subdocuments

 education: [ { "courseName": "Java", "from" : ISODate("2010-12-08T00:00:00.000Z"), "to" : ISODate("2011-05-31T00:00:00.000Z"), "isGoingOn": false }, { "courseName": "PHP", "from" : ISODate("2013-12-08T00:00:00.000Z"), "to" : ISODate("2015-05-31T00:00:00.000Z"), "isGoingOn": false }, { "courseName": "Mysql", "from" : ISODate("2017-02-08T00:00:00.000Z"), "to" : null, "isGoingOn": true } ] 

Another point is this: this date may not be continuous in one sub-document to another sub-document. A user can have a course for 1 year, and then two years later he / she started his next course for 1 year and 3 months (this means that this user has a total of 2 years and a 3-month course duration), I I want to get the date difference of each subdocument in the array of entities and summarize them. Suppose in my Java data samples the duration of the course is 6 months and 22 days, the duration of the PHP course is 1 year, 6 months and 22 days, and the last is from February 8, 2017 to the present, and this happens on, so my duration Learning is the sum of these intervals.

Answer 1

Please try this aggregation to get the difference in dates in days, months and years, add a few steps in the $addFields and reduce the difference to date, the range of months without overflow, and the assumption is 1 month = 30 days

the pipeline

 db.edu.aggregate( [ { $addFields : { trainingPeriod : { $map : { input : "$education", as : "t", in : { year: {$subtract: [{$year : {$ifNull : ["$$t.to", new Date()]}}, {$year : "$$t.from"}]}, month: {$subtract: [{$month : {$ifNull : ["$$t.to", new Date()]}}, {$month : "$$t.from"}]}, dayOfMonth: {$subtract: [{$dayOfMonth : {$ifNull : ["$$t.to", new Date()]}}, {$dayOfMonth : "$$t.from"}]} } } } } }, { $addFields : { trainingPeriod : { $map : { input : "$trainingPeriod", as : "d", in : { year: "$$d.year", month: {$cond : [{$lt : ["$$d.dayOfMonth", 0]}, {$subtract : ["$$d.month", 1]}, "$$d.month" ]}, day: {$cond : [{$lt : ["$$d.dayOfMonth", 0]}, {$add : [30, "$$d.dayOfMonth"]}, "$$d.dayOfMonth" ]} } } } } }, { $addFields : { trainingPeriod : { $map : { input : "$trainingPeriod", as : "d", in : { year: {$cond : [{$lt : ["$$d.month", 0]}, {$subtract : ["$$d.year", 1]}, "$$d.year" ]}, month: {$cond : [{$lt : ["$$d.month", 0]}, {$add : [12, "$$d.month"]}, "$$d.month" ]}, day: "$$d.day" } } } } }, { $addFields : { total : { $reduce : { input : "$trainingPeriod", initialValue : {year : 0, month : 0, day : 0}, in : { year: {$add : ["$$this.year", "$$value.year"]}, month: {$add : ["$$this.month", "$$value.month"]}, day: {$add : ["$$this.day", "$$value.day"]} } } } } }, { $addFields : { total : { year : "$total.year", month : {$add : ["$total.month", {$floor : {$divide : ["$total.day", 30]}}]}, day : {$mod : ["$total.day", 30]} } } }, { $addFields : { total : { year : {$add : ["$total.year", {$floor : {$divide : ["$total.month", 12]}}]}, month : {$mod : ["$total.month", 12]}, day : "$total.day" } } } ] ).pretty() 

result

 { "_id" : ObjectId("5a895d4721cbd77dfe857f95"), "education" : [ { "courseName" : "Java", "from" : ISODate("2010-12-08T00:00:00Z"), "to" : ISODate("2011-05-31T00:00:00Z"), "isGoingOn" : false }, { "courseName" : "PHP", "from" : ISODate("2013-12-08T00:00:00Z"), "to" : ISODate("2015-05-31T00:00:00Z"), "isGoingOn" : false }, { "courseName" : "Mysql", "from" : ISODate("2017-02-08T00:00:00Z"), "to" : null, "isGoingOn" : true } ], "trainingPeriod" : [ { "year" : 0, "month" : 5, "day" : 23 }, { "year" : 1, "month" : 5, "day" : 23 }, { "year" : 1, "month" : 0, "day" : 10 } ], "total" : { "year" : 2, "month" : 11, "day" : 26 } } > 

Answer 2

Well, you can just use the existing date aggregation operator , rather than use the math to convert to "days" like you do now:

 db.getCollection('user').aggregate([ { "$unwind": "$education" }, { "$group": { "_id": "$_id", "years": { "$sum": { "$subtract": [ { "$subtract": [ { "$year": { "$ifNull": [ "$education.to", new Date() ] } }, { "$year": "$education.from" } ]}, { "$cond": { "if": { "$gt": [ { "$month": { "$ifNull": [ "$education.to", new Date() ] } }, { "$month": "$education.from" } ] }, "then": 0, "else": 1 }} ] } }, "months": { "$sum": { "$add": [ { "$subtract": [ { "$month": { "$ifNull": [ "$education.to", new Date() ] } }, { "$month": "$education.from" } ]}, { "$cond": { "if": { "$gt": [ { "$month": { "$ifNull": ["$education.to", new Date() ] } }, { "$month": "$education.from" } ] }, "then": 0, "else": 12 }} ] } }, "days": { "$sum": { "$add": [ { "$subtract": [ { "$dayOfYear": { "$ifNull": [ "$education.to", new Date() ] } }, { "$dayOfYear": "$education.from" } ]}, { "$cond": { "if": { "$gt": [ { "$month": { "$ifNull": [ "$education.to", new Date() ] } }, { "$month": "$education.from" } ] }, "then": 0, "else": 365 }} ] } } }}, { "$project": { "years": { "$add": [ "$years", { "$add": [ { "$floor": { "$divide": [ "$months", 12 ] } }, { "$floor": { "$divide": [ "$days", 365 ] } } ]} ] }, "months": { "$mod": [ { "$add": [ "$months", { "$floor": { "$multiply": [ { "$divide": [ "$days", 365 ] }, 12 ] }} ]}, 12 ] }, "days": { "$mod": [ "$days", 365 ] } }} ]) 

This is a “kind of” approximation to the “days” and “months” without the need for “certain” leap years, but it will bring you a result that should be “close enough” for most purposes.

You can even do this without $unwind if your version of MongoDB is 3.2 or higher:

 db.getCollection('user').aggregate([ { "$addFields": { "duration": { "$let": { "vars": { "edu": { "$map": { "input": "$education", "as": "e", "in": { "$let": { "vars": { "toDate": { "$ifNull": ["$$e.to", new Date()] } }, "in": { "years": { "$subtract": [ { "$subtract": [ { "$year": "$$toDate" }, { "$year": "$$e.from" } ]}, { "$cond": { "if": { "$gt": [{ "$month": "$$toDate" },{ "$month": "$$e.from" }] }, "then": 0, "else": 1 }} ] }, "months": { "$add": [ { "$subtract": [ { "$ifNull": [{ "$month": "$$toDate" }, new Date() ] }, { "$month": "$$e.from" } ]}, { "$cond": { "if": { "$gt": [{ "$month": "$$toDate" },{ "$month": "$$e.from" }] }, "then": 0, "else": 12 }} ] }, "days": { "$add": [ { "$subtract": [ { "$ifNull": [{ "$dayOfYear": "$$toDate" }, new Date() ] }, { "$dayOfYear": "$$e.from" } ]}, { "$cond": { "if": { "$gt": [{ "$month": "$$toDate" },{ "$month": "$$e.from" }] }, "then": 0, "else": 365 }} ] } } } } } } }, "in": { "$let": { "vars": { "years": { "$sum": "$$edu.years" }, "months": { "$sum": "$$edu.months" }, "days": { "$sum": "$$edu.days" } }, "in": { "years": { "$add": [ "$$years", { "$add": [ { "$floor": { "$divide": [ "$$months", 12 ] } }, { "$floor": { "$divide": [ "$$days", 365 ] } } ]} ] }, "months": { "$mod": [ { "$add": [ "$$months", { "$floor": { "$multiply": [ { "$divide": [ "$$days", 365 ] }, 12 ] }} ]}, 12 ] }, "days": { "$mod": [ "$$days", 365 ] } } } } } } }} ]) 

This is because from MongoDB 3.4 you can use $sum directly with an array or any list of expressions in stages like $addFields or $project , and $map can use the same expressions of the “date aggregation” operator for each element of the array instead of $unwind .

Thus, the basic mathematics can really be done in one part of the “decrease” of the array, and then each amount can be adjusted using the common “dividers” over the years, and “modulo” or “remainder” from any overruns in months and days.

Essentially returns:

 { "_id" : ObjectId("5a07688e98e4471d8aa87940"), "education" : [ { "courseName" : "Java", "from" : ISODate("2010-12-08T00:00:00.000Z"), "to" : ISODate("2011-05-31T00:00:00.000Z"), "isGoingOn" : false }, { "courseName" : "PHP", "from" : ISODate("2013-12-08T00:00:00.000Z"), "to" : ISODate("2015-05-31T00:00:00.000Z"), "isGoingOn" : false }, { "courseName" : "Mysql", "from" : ISODate("2017-02-08T00:00:00.000Z"), "to" : null, "isGoingOn" : true } ], "duration" : { "years" : 3.0, "months" : 3.0, "days" : 259.0 } } 

Considering November 11, 2017

Answer 3

You can simplify your code using client-side processing with moment js .

All time time math is handled by the js time library. Use duration to calculate shorter diff time

Use the abbreviation to add a time difference for all elements of the array, followed by a point in time, to display the time in years / months / days.

He solves two questions:

• Gives you the exact difference between months and days between two dates.
• Gives the expected format.

For example:

 var education = [ { "courseName": "Java", "from" : new Date("2010-12-08T00:00:00.000Z"), "to" : new Date("2011-05-31T00:00:00.000Z"), "isGoingOn": false }, { "courseName": "PHP", "from" : new Date("2013-12-08T00:00:00.000Z"), "to" : new Date("2015-05-31T00:00:00.000Z"), "isGoingOn": false }, { "courseName": "Mysql", "from" : new Date("2017-02-08T00:00:00.000Z"), "to" : null, "isGoingOn": true } ]; var reducedDiff = education.reduce(function(prevVal, elem) { if(elem.isGoingOn) elem.to = new Date(); var diffDuration = moment(elem.to).diff(moment(elem.from)); return prevVal + diffDuration; }, 0); var duration = moment.duration(reducedDiff); alert(duration.years() +" y, " + duration.months() + " m, " + duration.days() + " d " ); var durationstr = duration.years() +" y, " + duration.months() + " m, " + duration.days() + " d "; 

MongoDb Integration:

 var reducedDiff = db.getCollection('user').find({},{education:1}).reduce(function(...