MongoDB: Aggregate query to sum duration field

Question

I have set of records from where I need to sum up duration to be displayed

Records

[{   
  id:"1",
  duration:"07:30:00"
},
{   
  id:"1",
  duration:"07:30:00"
}
{   
  id:"2",
  duration:"07:30:00"
}]

Output

[{
  _id: 1,
  totalDuration: "15:00"
},{
  _id: 2,
  totalDuration: "07:30"
}]

Dheemanth Bhat · Answer 1 · 2021-03-26T15:17:40.400

From MongoDB version >= 4.4 you can use the $function operator to define custom functions to implement behavior not supported by the MongoDB Query Language. Logic to add two time-data in HH:MM:SS format is taken from here.

So try this query:

db.testCollection.aggregate([
    {
        $group: {
            _id: "$id",
            times: { $push: "$duration" }
        }
    },
    {
        $project: {
            _id: 1,
            totalDuration: {
                $function: {
                    body: function(times) {
                        let hours = 0;
                        let minutes = 0;
                        let seconds = 0;

                        for (let i = 0; i < times.length; i++) {
                            let values = times[i].split(":");

                            hours += parseInt(values[0]);
                            minutes += parseInt(values[1]);
                            seconds += parseInt(values[2]);
                        }

                        let realHrs = hours + Math.floor(minutes / 60);
                        let realMins = (minutes % 60) + Math.floor(seconds / 60);
                        let realSecs = seconds % 60;

                        return realHrs + ":" + realMins + ":" + realSecs
                    },
                    args: ["$times"],
                    lang: "js"
                }
            }
        }
    },
    {
        $sort: { _id: 1 }
    }
]);

Output

/* 1 */
{
    "_id" : "1",
    "totalDuration" : "15:6:12"
},

/* 2 */
{
    "_id" : "2",
    "totalDuration" : "7:30:0"
}

Data in testCollection collection:

{
    "_id" : ObjectId("..."),
    "id" : "1",
    "duration" : "07:30:30"
},
{
    "_id" : ObjectId("..."),
    "id" : "1",
    "duration" : "07:35:42"
},
{
    "_id" : ObjectId("..."),
    "id" : "2",
    "duration" : "07:30:00"
}

If you are using this solution the plz thoroughly test the logic inside the `$function` `body` for edge cases. I have not tested it thoroughly. — Dheemanth Bhat, Mar 26 '21 at 12:07

score 1 · Answer 2 · answered Mar 26 '21 at 11:48

There is no any straight way to do this in mongodb, You can use $function operator starting from MongoDB v4.4 you can write custom logic in JS syntax,

Second option you can do custom logic using mongodb arithmetic operators,

$split to split duration string using ":" to array and set it to time variable in $let
$arrayElemAt to get specific element from array, we are taking hour and minute from 0 and 1 position
$toInt to convert string to integer
check condition if minute is 00 then return o otherwise 0.5
$add to sum hour and minute
$group by id and sum duration

db.collection.aggregate([
  {
    $addFields: {
      duration: {
        $let: {
          vars: {
            time: { $split: ["$duration", ":"] }
          },
          in: {
            $add: [
              { $toInt: { $arrayElemAt: ["$$time", 0] } },
              {
                $cond: [
                  { $eq: [{ $arrayElemAt: ["$$time", 1] }, "00"] },
                  0,
                  0.5
                ]
              }
            ]
          }
        }
      }
    }
  },
  {
    $group: {
      _id: "$id",
      duration: { $sum: "$duration" }
    }
  }
])

Playground

The query will result:

[
  {
    "_id": "1",
    "duration": 15
  },
  {
    "_id": "2",
    "duration": 7.5
  }
]

score 1 · Answer 3 · answered Mar 26 '21 at 17:39

Another solution could be this one:

db.collection.aggregate([
   {
      $addFields: {
         duration: {
            $let: {
               vars: { time: { $split: ["$duration", ":"] } },
               in: {
                  $dateFromParts: {
                     year: 1970, month: 1, day: 1,
                     hour: { $toInt: { $arrayElemAt: ["$$time", 0] } },
                     minute: { $toInt: { $arrayElemAt: ["$$time", 1] } },
                     second: { $toInt: { $arrayElemAt: ["$$time", 2] } }
                  }
               }
            }
         }
      }
   },
   { $group: { _id: "$id", duration: { $sum: { $toLong: "$duration" } } } },
   {
      $set: {
         duration: {
            $dateToString: {
               date: {
                  $dateFromParts: {
                     year: 1970, month: 1, day: 1, millisecond: "$duration"
                  }
               },
               format: "%H:%M:%S"
            }
         }
      }
   }
])

However, if the duration is longer than 24 hours then it does not work anymore. But you may add { $dayOfYear: <dateExpression> } to the output, then the duration may go up to one year.

MongoDB: Aggregate query to sum duration field

3 Answers3

Linked