MongoDB Charts how to get time difference of createdAt and updatedAt

Question

sample data.

"id": 1
"createdAt": {
     "$date": "2020-09-29T01:52:10.199Z"
},
"updatedAt": {
     "$date": "2020-09-29T02:55:10.199Z"
}

"id": 2
"createdAt": {
     "$date": "2020-09-29T01:52:10.199Z"
},
"updatedAt": {
     "$date": "2020-09-29T01:55:10.199Z"
}

Code:

db.orders.aggregate([ 
{ 
    $project: { 
        products: 1, 
        dateDifference: { 
            $subtract: [ "$createdAt", "$updatedAt" ] 
        } 
    } 
},
{
    $unset: "_id"
}
])

Output:

"id": 1
"createdAt": {
     "$date": "2020-09-29T01:52:10.199Z"
},
"updatedAt": {
     "$date": "2020-09-29T01:55:10.199Z"
}
timeDifference: -695752959

Expected Output

"id": 1
"createdAt": {
     "$date": "2020-09-29T01:52:10.199Z"
},
"updatedAt": {
     "$date": "2020-09-29T01:55:10.199Z"
}
timeDifference: 1 hr 3 minutes / 1:03:00 any of the 2

"id": 2
"createdAt": {
     "$date": "2020-09-29T01:52:10.199Z"
},
"updatedAt": {
     "$date": "2020-09-29T01:55:10.199Z"
}
timeDifference: 3 minutes / 0:03:00 any of the 2

averageDiff: 30 minutes // sample

I want to get the time difference for my createdAt and updatedAt fields and get the average of the time so that I can display on my MongoDB charts. Any suggestsions on how to do it?

Answer 1

The best you can have this

db.collection.aggregate([
  {
    $project: {
      dateDifference: {
        "$divide": [
          {
            $abs: {
              $subtract: [
                "$createdAt",
                "$updatedAt"
              ]
            }
          },
          3600000
        ]
      }
    }
  }
])

It results in hours. By changing the number, you can have it in days/seconds. Default you get milliseconds.

Answer 2

db.orders.aggregate([ { 
$project: { 
    products: 1, 
  dateDifference: {$divide: [{$subtract: ["$updatedAt", "$createdAt"]}, 3600000]}
} }])

This query will provide timestamp in hours.