Calculate date difference 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 is:

{
    "_id" : ObjectId("59fabb20d7905ef056f55ac1"),
    "duration" : 2.34794520547945
}

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

But what I want to do is get its duration in year+ month + day format, something like: 2 y, 3 m, 20 d. One another point, if a course is going on the to field is null, and another field isGoingOn: true, so here I should calculate the duration by using current date instead of to field. And user has 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
   }
]

One another point is this: that date may be not continuous in one subdocument to the other subdocument. A user may have a course for 1 year, and then after two years, he/she started his/her next course for 1 year, and 3 months (it means this user has a total of 2 years and 3-month course duration). What I want is get date difference of each subdocument in educations array, and sum those. Suppose in my sample data Java course duration is 6 month, and 22 days, PHP course duration is 1 year, and 6 months, and 22 days, and the last one is from 8 Feb 2017 till now, and it's going on, so my education duration is the sum of these intervals.

Answer 1

Please try this aggregation to get date difference in days,months and years, added multiple $addFields stage compute and reduce differences to date, month range without underflow, and the assumption here is 1 month = 30 days

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
    }
}
>