Convert date difference to years to calculate age in MongoDB

Question

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

sidgate · Accepted Answer · 2021-09-14T03:09:49.697

Update: MongoDB 5.0 solution, will consider leap years as well

db.collection.aggregate([
  { $addFields:
    { age: { $dateDiff: { startDate: "$dob", endDate: "$$NOW", unit: "year" } } }
  }
])

Update: 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)]
                }
            }
        } 
     } 
} ] )

To do it right you could use one of the approaches from here: https://stackoverflow.com/questions/4060004/calculate-age-in-javascript — JohnnyHK, Sep 08 '16 at 03:44

score 4 · Answer 2 · answered Jan 08 '20 at 09:55

4

The accepted answer is incorrect by as many days as there are a leap years that passed since the person was born. Here's a more correct way to calculate age:

{$subtract:[
   {$subtract:[{$year:"$$NOW"},{$year:"$dateOfBirth"}]},
   {$cond:[
      {$gt:[0, {$subtract:[{$dayOfYear:"$$NOW"},
      {$dayOfYear:"$dateOfBirth"}]}]},
      1,
      0
   ]}
]}

answered Jan 08 '20 at 09:55

Asya Kamsky

40,756
5
103
127

could you please explain this further? the last integers 1 and 0, why are they there? – Miko Chu Jul 28 '21 at 01:51
1

Your age depends on whether or not we’ve passed your birthday in the current year. – Asya Kamsky Jul 28 '21 at 13:32

Xavier Guihot · Answer 3 · 2021-07-13T16:17:13.003

Starting in Mongo 5.0, it's a perfect use case for the new $dateDiff aggregation operator:

// { "dob" : ISODate("1990-03-27") }
// { "dob" : ISODate("2008-08-07") }
db.collection.aggregate([
  { $addFields:
    { age: { $dateDiff: { startDate: "$dob", endDate: "$$NOW", unit: "year" } } }
  }
])
// { "dob" : ISODate("1990-03-27"), "age" : 31 }
// { "dob" : ISODate("2008-08-07"), "age" : 13 }

This nicely fits the age scenario since (quoting the documentation) durations produced by $dateDiff are measured by counting the number of times a unit boundary is passed. For example, two dates that are 18 months apart would return 1 year difference instead of 1.5 years.

Also note that $$NOW is a variable that returns the current datetime value.

Convert date difference to years to calculate age in MongoDB

3 Answers3

Linked