Mongodb collection objects total hours collection

user_13213687 0
Viewed 50

mongodb collection data (thanks advance for this)

{
    "_id" : ObjectId("5f7b3d78e95af70e17efd6d6"),
    "employeeId" : "2707",
    "employeeName" : "HrJosh",
    "status" : "Present",
    "date" : "2020-10-16",
    "clientName" : "Lorent",
    "projectName" : "ChatBot",
    "taskName" : "Learning",
    "durationHrs" : "09:30",
    "createdAt" : ISODate("2020-10-05T15:36:24.677Z"),
    "updatedAt" : ISODate("2020-10-05T15:36:24.677Z"),
    "__v" : 0
}
{
    "_id" : ObjectId("5f7b5c3de95af70e17efd6d7"),
    "employeeId" : "2707",
    "employeeName" : "Gowdham",
    "status" : "Present",
    "date" : "2020-10-01",
    "clientName" : "Lorent",
    "projectName" : "ChatBot",
    "taskName" : "Meeting",
    "durationHrs" : "09:30",
    "createdAt" : ISODate("2020-10-05T17:47:41.381Z"),
    "updatedAt" : ISODate("2020-10-05T17:47:41.381Z"),
    "__v" : 0
}
{
    "_id" : ObjectId("5f7b5c62e95af70e17efd6d8"),
    "employeeId" : "2707",
    "employeeName" : "Gowdham",
    "status" : "Present",
    "date" : "2020-10-02",
    "clientName" : "fsdsdsd",
    "projectName" : "ChatBotsd",
    "taskName" : "Manage Team",
    "durationHrs" : "09:30",
    "createdAt" : ISODate("2020-10-05T17:48:18.994Z"),
    "updatedAt" : ISODate("2020-10-05T17:48:18.994Z"),
    "__v" : 0
}

we need output like total duration hours for calculate above three objects durationHrs:((09:30)+(09:30)+(09:30)) , Output be like. Total Duration Hour : 28hrs:30mins

mongoose mongodb
2 Answers

I would suggest that you make durationHrs in your schema as Number field, and Store the number of minutes in it, that way it will be easy to calculate and sum all the fields using simple aggregation query.

But, if you cant change it, and want to get the answer using the current schema, you can try below aggregation, though i cant guarantee performance, it might be slow due to use of many aggregation stages and operators.

Try this:

db.collection.aggregate([
  {
    $project: {
      time: {
        $split: [
          "$durationHrs",
          ":"
        ]
      }
    }
  },
  {
    "$project": {
      totalTime: {
        $sum: [
          {
            "$multiply": [
              {
                "$toInt": {
                  "$arrayElemAt": [
                    "$time",
                    0
                  ]
                }
              },
              60
            ]
          },
          {
            "$toInt": {
              "$arrayElemAt": [
                "$time",
                1
              ]
            }
          }
        ]
      }
    }
  },
  {
    $group: {
      _id: null,
      durationHrs: {
        $sum: "$totalTime"
      }
    }
  },
  {
    $project: {
      durationHrs: {
        "$concat": [
          {
            "$toString": {
              $toInt: {
                "$divide": [
                  "$durationHrs",
                  60
                ]
              }
            }
          },
          ":",
          {
            "$toString": {
              $mod: [
                "$durationHrs",
                60
              ]
            }
          }
        ]
      }
    }
  }
])

The summary of the aggregation pipeline is that, you split the durations, convert it into minutes, add the minutes and convert back into the desired format that you have.

The result will be :

[
  {
    "_id": null,
    "durationHrs": "28:30"
  }
]

You can play with the query on Mongo Playground

You can read about all the operators used in the query in official MongoDB docs.

user_50840000

My proposal would be this one:

db.collection.aggregate([
   {
      $addFields: {
         duration: {
            $toLong: {
               $dateFromParts: {
                  year: 1970,
                  hour: { $toInt: { $first: { $split: ["$durationHrs", ":"] } } },
                  minute: { $toInt: { $last: { $split: ["$durationHrs", ":"] } } },
               }
            }
         }
      }
   },
   { $group: { _id: null, durations: { $sum: "$duration" } } },
   {
      $set: {
         h: {
            $toInt: {
               $sum: [{
                  $multiply: [
                     { $dayOfYear: { $toDate: "$durations" } }, 24]
               },
               -24,
               { $hour: { $toDate: "$durations" } }]
            }
         },
         m: { $minute: { $toDate: "$durations" } }
      }
   },
   { $project: { durations: { $concat: [{ $toString: "$h" }, ":", { $toString: "$m" }] } } }
])

Be aware, it works only for duration's up to one year, i.e. 366 days.

user_30272660
Related