mongoose - MongoDB: Calculate dwell time between every status value change

Question

I want to find out the dwell time between every presenceStatus change.

Example collection -

   /* 1 */

{
    "_id" : ObjectId("5e4889a7c7959f6a13039902"),
    "presenceStatus" : 0,
    "createdAt" : ISODate("2020-02-16T00:14:35.121Z"),
    "updatedAt" : ISODate("2020-02-16T00:14:35.121Z"),
    "__v" : 0
}
/* 2 */
{
    "_id" : ObjectId("5e4889a7c7959f6a1303990c"),
    "presenceStatus" : 1,
    "createdAt" : ISODate("2020-02-16T00:15:35.121Z"),
    "updatedAt" : ISODate("2020-02-16T00:15:35.121Z"),
    "__v" : 0
}
/* 3 */

{
    "_id" : ObjectId("5e4889a9c7959f6a1303995c"),
    "presenceStatus" : 1,
    "sensingTime" : ISODate("2020-02-16T00:15:37.000Z"),
    "createdAt" : ISODate("2020-02-16T00:15:37.420Z"),
    "updatedAt" : ISODate("2020-02-16T00:15:37.420Z"),
    "__v" : 0
}

/* 4 */
{
    "_id" : ObjectId("5e4889b0c7959f6a130399ff"),
    "presenceStatus" : 1,
    "createdAt" : ISODate("2020-02-16T00:15:44.316Z"),
    "updatedAt" : ISODate("2020-02-16T00:15:44.316Z"),
    "__v" : 0
}

/* 5 */
{
    "_id" : ObjectId("5e4889b3c7959f6a13039a58"),
    "presenceStatus" : 1,
    "sensingTime" : ISODate("2020-02-16T00:15:47.000Z"),
    "createdAt" : ISODate("2020-02-16T00:15:47.181Z"),
    "updatedAt" : ISODate("2020-02-16T00:15:47.181Z"),
    "__v" : 0
}

/* 6 */
{
    "_id" : ObjectId("5e4889b5c7959f6a13039aad"),
    "presenceStatus" : 1,
    "sensingTime" : ISODate("2020-02-16T00:15:49.000Z"),
    "createdAt" : ISODate("2020-02-16T00:15:49.545Z"),
    "updatedAt" : ISODate("2020-02-16T00:15:49.545Z"),
    "__v" : 0
}

/* 7 */
{
    "_id" : ObjectId("5e4889b9c7959f6a13039b28"),
    "presenceStatus" : 1,
    "sensingTime" : ISODate("2020-02-16T00:15:53.000Z"),
    "createdAt" : ISODate("2020-02-16T00:15:53.389Z"),
    "updatedAt" : ISODate("2020-02-16T00:15:53.389Z"),
    "__v" : 0
}

/* 8 */
{
    "_id" : ObjectId("5e4889bcc7959f6a13039b78"),
    "presenceStatus" : 1,
    "sensingTime" : ISODate("2020-02-16T00:15:56.000Z"),
    "createdAt" : ISODate("2020-02-16T00:15:56.007Z"),
    "updatedAt" : ISODate("2020-02-16T00:15:56.007Z"),
    "__v" : 0
}

/* 9 */
{
    "_id" : ObjectId("5e4889bfc7959f6a13039c00"),
    "presenceStatus" : 1,
    "sensingTime" : ISODate("2020-02-16T00:15:59.000Z"),
    "createdAt" : ISODate("2020-02-16T00:15:59.619Z"),
    "updatedAt" : ISODate("2020-02-16T00:15:59.619Z"),
    "__v" : 0
}
/* 10 */
{
    "_id" : ObjectId("5e4889c2c7959f6a13039c4a"),
    "presenceStatus" : 0,
    "sensingTime" : ISODate("2020-02-16T00:16:02.000Z"),
    "createdAt" : ISODate("2020-02-16T00:16:02.100Z"),
    "updatedAt" : ISODate("2020-02-16T00:16:02.100Z"),
    "__v" : 0
}

As you can see presenceStatus changed in 2nd document from value 0 to 1.

I want to record this dwell Time period when the status remained 1. (basically time difference between status = 1 to status = 0 )

The same process starts again when it finds presenceStatus 1 again and records the time-period when presenceStatus remained 1 for that chunk of data.

Result collection would look like -

{
    "_id" : xxx
    "occupiedTime" : ISODate("2020-02-16T00:15:35.121Z"), //  *updatedAt* value of document 2, cause that's when it changed to 1. 

    "vacantTime" : ISODate("2020-02-16T00:16:02.100Z"), // *updatedAt* of document 10, because that's when status changed from 0 to 1. 
    "dwellTime" : (vacant time - occupied time in HH:MM:SS)
    "created" : "2019-05-29 07:08:13",
    "__v" : 0
}

Your help is appreciated.

Answer 1

Check if this solution meets your requirements.

Explanation

1. We join over the same collection. So for each item _i we take item _i+1. This method gives us where presenceStatus has been changed.
2. We filter document_{i i+1} pairs where presenceStatus is 0 - 1 or 1 - 0.
3. We group them into single data array.
4. Now we iterate over data by 2 steps (i=0;i<data.length;i+=2) and take updatedAt value.

    var occupiedTime = data[i].tmp.updatedAt
    var vacantTime   = data[i+1].tmp.updatedAt

5. We flatten calculated values and restore original document structure.

---

db.collection.aggregate([
  {
    $lookup: {
      from: "collection",
      let: {
        root_id: "$_id"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $gt: [
                "$_id",
                "$$root_id"
              ]
            }
          }
        },
        {
          $limit: 1
        }
      ],
      as: "tmp"
    }
  },
  {
    $match: {
      $or: [
        {
          "presenceStatus": 1,
          "tmp.presenceStatus": 0
        },
        {
          "presenceStatus": 0,
          "tmp.presenceStatus": 1
        }
      ]
    }
  },
  {
    $group: {
      _id: null,
      data: {
        $push: {
          $mergeObjects: [
            "$$ROOT",
            {
              tmp: {
                $arrayElemAt: [
                  "$tmp",
                  0
                ]
              }
            }
          ]
        }
      }
    }
  },
  {
    $addFields: {
      data: {
        $map: {
          input: {
            $range: [
              0,
              {
                $size: "$data"
              },
              2
            ]
          },
          as: "idx",
          in: {
            "occupiedTime": {
              $arrayElemAt: [
                "$data.tmp.updatedAt",
                {
                  $cond: [
                    {
                      $eq: [
                        {
                          $arrayElemAt: [
                            "$data.tmp.presenceStatus",
                            "$$idx"
                          ]
                        },
                        1
                      ]
                    },
                    "$$idx",
                    {
                      $add: [
                        "$$idx",
                        1
                      ]
                    }
                  ]
                }
              ]
            },
            "vacantTime": {
              $arrayElemAt: [
                "$data.tmp.updatedAt",
                {
                  $cond: [
                    {
                      $eq: [
                        {
                          $arrayElemAt: [
                            "$data.tmp.presenceStatus",
                            "$$idx"
                          ]
                        },
                        0
                      ]
                    },
                    "$$idx",
                    {
                      $add: [
                        "$$idx",
                        1
                      ]
                    }
                  ]
                }
              ]
            },
            "created": {
              $arrayElemAt: [
                "$data.tmp.createdAt",
                "$$idx"
              ]
            },
            "_id": {
              $arrayElemAt: [
                "$data.tmp._id",
                "$$idx"
              ]
            },
            "__v": 0
          }
        }
      }
    }
  },
  {
    $unwind: "$data"
  },
  {
    $replaceRoot: {
      newRoot: "$data"
    }
  },
  {
    $addFields: {
      "dwellTime": {
        $dateToString: {
          date: {
            $toDate: {
              $subtract: [
                "$vacantTime",
                "$occupiedTime"
              ]
            }
          },
          format: "%H-%M-%S"
        }
      }
    }
  }
])

MongoPlayground