mongodbdatemongoosedate-arithmetic

Convert date difference to years to calculate age in MongoDB


I am using following to calculate age in timestamp difference.

db.getCollection('person').aggregate( [
  { $project: { 
    item: 1, 
    DOB: "$personal.DOB",
    dateDifference: { $subtract: [ new Date(), "$personal.DOB" ] }
  } } 
] )

I get the numeric value in dateDifference. I want to convert it to years by dividing it with (365*24*60*60*1000). But I don't know how to specify this formula in above query. I have tried the following, but it does not return any value

db.getCollection('person').aggregate( [ 
  { $project: { 
    item: 1, 
    DOB:"$personal.DOB", 
    dateDifference: ({ $subtract: [ new Date(), "$personal.DOB" ] })/(365*24*60*60*1000)
   } } 
] )

Solution

  • Update: This answer was for pre Mongo 5.0. Refer to other answers for latest mongo version.

    We can just combine the aggregation operators. Note that this solution won't give accurate result as it does not consider leap years

    db.getCollection('person').aggregate( [ { 
        $project: { 
            date:"$demographics.DOB", 
            age: { 
                $divide: [{$subtract: [ new Date(), "$Demographics.DOB" ] }, 
                        (365 * 24*60*60*1000)]
            } 
         } 
    } ] )
    

    Old solution with $let


    I was able to solve the issue with $let expression

    db.getCollection('person').aggregate( [ { 
        $project: { 
            item: 1, 
            date:"$demographics.DOB", 
            age: { 
                $let:{
                    vars:{
                        diff: { 
                            $subtract: [ new Date(), "$demographics.DOB" ] 
                        }
                    },
                    in: {
                        $divide: ["$$diff", (365 * 24*60*60*1000)]
                    }
                }
            } 
         } 
    } ] )