Date diff between data in different documents

[pseudo.charles (Charles Fonseca)]

I have a document like this one:

{
  _id: ObjectId(...),
  date: [ISODate('2022-05-27T00:00:00.000+00:00'), ISODate('2022-05-28T00:00:00.000+00:00')]
}

I need to calculate the difference between each date and the next one in days.
Can you help me with it?

[pseudo.charles (Charles Fonseca)]

I tried using unwind, keeping the index, and grouping by the index and the next value, but it didn’t work

[Satyam (Satyam Gupta)]

Hi @pseudo.charles,

Welcome to the MongoDB Community Forums!

If I understand the question correctly, you need the difference between the two dates in your date field. Can you please let us know why you are trying to do this? Based on the document you provided, you can try the $dateDiff which would return the difference between the two dates. To select elements from the array, you can use $arrayElemAt which returns the element at the specified array index.

This is what the query would look like, there are other fields in $dateDiff too that you can use according to your use case. Also, note the use of arrayElemAt indexes as 0 (the first element) and -1 (the last element), you can adjust the indexes as per your data:

db.collection.aggregate([
  {
    $project: {
      dateDiff: {
        $dateDiff: {
          startDate: {
            $arrayElemAt: [
              "$date",
              0
            ]
          },
          endDate: {
            $arrayElemAt: [
              "$date",
              -1
            ]
          },
          "unit": "day",
          
        }
      }
    }
  }
])

Please let us know if you have any further questions. Feel free to reach out for anything else as well.

Regards,
Satyam

[pseudo.charles (Charles Fonseca)]

@Satyam

Can you please let us know why you are trying to do this? Based on the document you provided, you can try the $dateDiff which would return the difference between the two dates. To select elements from the array, you can use $arrayElemAt which returns the element at the specified array index.

This is what the query would look like, there are other fields in $dateDiff too that you can use according to your use case. Also, note the use of arrayElemAt indexes as 0 (the first element) and -1 (the last element), you can adjust the indexes as per your data:

Thank you a lot @Satyam! The thing is that the date array can have more than 2 elements, and if so, I’d like to know the date difference between each index and the current one. In the case you mentioned, it’s always the first and the last one

[pseudo.charles (Charles Fonseca)]

I’d like to know the date difference between each index and the current one.

Actually, the date difference between each index and the next one.

[steevej (Steeve Juneau)]

You do the same logic but within a $map. You will end up with an array od date differences.

[pseudo.charles (Charles Fonseca)]

Thank you @steevej! This solves the issue:

{
  $project: {
    _id: 0,
    dateDiff: {
      $map: {
        input: {
          $range: [0, { $size: '$date' }]
        },
        as: 'this',
        in: {
          $dateDiff: {
            startDate: {
              $arrayElemAt: ['$date', '$$this']
            },
            endDate: {
              $arrayElemAt: ['$date', {
                $add: ['$$this', 1]
              }
              ]
            },
            unit: 'day'
          }
        }
      }
    }
  }
}