Average interval time in Hrs, Minutes, seconds Mongodb

I am trying to get the average time interval for all the documents in a collection.

The output I am trying to figure out is in this format Average: HH:MM:SS

It will compute the time interval for each document and then aggregate it to get the average time interval for the whole data set.

This is sample data.

{
    "_id" : ObjectId("60dc1e7d72296329347b2bbe"),
    "name": "firstupdate",
    "starttime" : ISODate("2021-06-30T07:38:06.926Z"),
    "endtime" : ISODate("2021-06-30T12:35:08.265Z"),
},
{
    "_id" : ObjectId("60dc1e7d72296329347b2bce"),
       "name": "secondupdate",
    "starttime" : ISODate("2021-07-29T07:41:06.926Z"),
    "endtime" : ISODate("2021-07-30T01:52:07.937Z"),
},
{
    "_id" : ObjectId("60dc1ff472d9f809d6d2f23e"),
      "name": "thirdupdate",
    "starttime" : ISODate("2021-07-15T07:43:06.926Z"),
    "endtime" : ISODate("2021-07-14T10:34:13.269Z"),
},
{
    "_id" : ObjectId("60dc204e03362e293a5f5014"),
        "name": "fourthupdate",
    "starttime" : ISODate("2021-07-21T05:11:23.654Z"),
    "endtime" : ISODate("2021-07-21T09:46:33.000Z"),
},
{
    "_id" : ObjectId("60dc21436a9e0e09f9a551ae"),
        "name": "fifthupdate",
    "starttime" : ISODate("2021-07-07T02:34:06.926Z"),
    "endtime" : ISODate("2021-07-07T08:11:06.926Z"),
},

Thank you in advance

Solution 1:

You can use this one:

db.collection.aggregate([
   {
      $group: {
         _id: null,
         diff: {
            $avg: {
               $dateDiff: {
                  startDate: "$starttime",
                  endDate: "$endtime",
                  unit: "millisecond"
               }
            }
         }
      }
   },
   {
      $set: {
         diff: {
            $dateToString: {
               date: { $toDate: "$diff" },
               format: "%H:%M:%S"
            }
         }
      }
   }
])

Optionally use format: "%j %H:%M:%S", %j is day of year, i.e. this would return valid output up to one year. Otherwise you would need some Date math.

Mongo Playground