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?

you will get milliseconds from your query, my suggestion it is better to do in client side language using that milliseconds. — turivishal, Dec 15 '20 at 06:45
Hi, @turivishal I'm planning to do it in MongoDB Charts. is this possible to get the average time of my _createdAt_ and _updatedAt_ fields? — Pinky Promise, Dec 15 '20 at 06:48

score 2 · Answer 1 · answered Dec 15 '20 at 06:51

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.

score 1 · Answer 2 · answered Dec 15 '20 at 06:53

1

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

This query will provide timestamp in hours.

answered Dec 15 '20 at 06:53

AartiVerma

136
4

MongoDB Charts how to get time difference of createdAt and updatedAt

2 Answers2