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

41,784
5
109
133

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

4

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.

edited Jul 13 '21 at 16:17

answered Jun 15 '21 at 20:12

Xavier Guihot

54,987
21
291
190

I am getting the following error with MongoDB 5.0.15: InvalidPipelineOperator Invalid $addFields :: caused by :: Unrecognized expression '$dateDiff' – Thomas Mar 16 '23 at 09:46
Hard to explain, the documentation says it's available as of version 5.0, but maybe your minor version's still too early. https://www.mongodb.com/docs/v5.0/reference/operator/aggregation/dateDiff/#mongodb-expression-exp.-dateDiff – Xavier Guihot Mar 16 '23 at 10:03

Taha Farooqui · Answer 4 · 2022-09-05T12:28:34.937

Update 2022: If the mongo version < 5. This query filters user whose ages are above 65+. Playground

db.collection.aggregate([
  {
    $match: {
      $expr: {
        $gt: [
          {
            $let: {
              vars: {
                diff: {
                  $subtract: [
                    new Date(),
                    {
                      "$toDate": "$birthday"
                    }
                  ]
                }
              },
              in: {
                $divide: [
                  "$$diff",
                  {
                    $multiply: [
                      365,
                      24,
                      60,
                      60,
                      1000
                    ]
                  }
                ]
              }
            }
          },
          65
        ]
      }
    }
  },
  
])

Convert date difference to years to calculate age in MongoDB

4 Answers4

Linked