Convert string to integer/ date to calculate the age difference in Mongosh-CodePudding

I have a dataset for the laureates of Nobel prize since its inception. The birth_date is a string that comes as '1949-03-28'. The year of the award comes as an integer eg. 2000. I'd like to know the age of each individual at the time of laureation.

I go as far as substringing the birth_date to extract the year. But I don't know how to convert it to int and subsequently calculate the subtraction between year(birth_date) and the year(laureation).

I've looked on other posts, but nothing that really encompasses everything I need to do

Any help?

CodePudding user response：

With a bit of gymnastics, you can convert both to a Date and diff them. It could look like this:

db.collection.aggregate([
  {
    $addFields: {
      ageWhenAwarded: {
        $dateDiff: {
          startDate: {
            $toDate: '$birth_date'
          },
          endDate: {
            $toDate: {
              $concat: [
                { $toString: '$award_year' },
                '-01-01'
              ],
            }
          },
          unit: 'year'
        }
      }
    }
  }
]);

This produces a document like so:

{
  _id: ObjectId("63b7555beb9dcf4f317b43ae"),
  award_year: 2000,
  birth_date: '1949-03-28',
  ageWhenAwarded: 51
}

The ages will be approximate, since Durations are measured by counting the number of times a unit boundary is passed, but you only have award_year anyway, so I suspect that's okay (the other option would be to convert the dates to ints, subtract them, and divide by the number of milliseconds in a year).