MongoDb query to calculate time between alternating on off switch-CodePudding

{
metadata:{
eventcode:100
}
power:on // this can be either on or off
time:1667984669//unix timestamp
}

My document looks something like this the power can be on or it can be off, given to and from time I have to calculate how many hours it was on, I am having trouble because in 1 day it can even have 100 on and 100 off values which means 200 documents, so how to calculate the number of operational hour(i.e time that the system was on) in mongodb query?

CodePudding user response：

in case someone in future faces the problem

       t.aggregate([
          {
            $match: {
              "metadata.eventCode": "100",
               actualtime:{$gte: 1662143400000,$lte:1662229799999}
            },
          },
          {
            $sort: { actualtime: 1 },
          },
          {
            $facet: {
              on: [
                {
                  $match: {
                    "metadata.type": "on",
                  },
                },
              ],
              off: [
                {
                  $match: {
                    "metadata.type": "off",
                  },
                },
              ],
            },
          },
        
        
          {
            $project: {
              operationalHours: {
                $sum: {
                  $map: {
                    input: {
                      $range: [
                        0,
                        {
                          $size: "$on",
                        },
                      ],
                    },
                    as:"el",
                    in: {
                      $subtract: [
                        {
                          $arrayElemAt: ["$off.actualtime", "$$el"],
                        },
                        {
                          $arrayElemAt: ["$on.actualtime", "$$el"],
                        },
                      ],
                    },
                  },
                },
              },
            },
          },
          
        ]);

CodePudding user response：

I would use this one:

db.collection.aggregate([
   { $set: { time: { $toDate: { $multiply: ["$time", 1000] } } } },
   {
      $setWindowFields: {
         partitionBy: "$metadata.eventcode",
         sortBy: { time: 1 },
         output: {
            off: {
               $last: "$time",
               window: { documents: ["current", 1] }
            }
         }
      }
   },
   { $match: { power: "on" } },
   {
      $set: {
         operationalHours: {
            $dateDiff: {
               startDate: "$time",
               endDate: "$off",
               unit: "hour"
            }
         }
      }
   }
])

You can customized it, let's assume you have glitch input data like this:

[
   { id: 1, power: 'on', time: 1667984669 },
   { id: 1, power: 'on', time: 1667984669 }, // accidentally inserted twice
   { id: 2, power: 'off', time: 1667984669   1000 },
   { id: 3, power: 'off', time: 1667984669   2000 },
   { id: 4, power: 'off', time: 1667984669   3000 }
]

then use

{
   $setWindowFields: {
      partitionBy: "$eventcode",
      sortBy: { time: 1 },
      output: {
         off: {
            $min: { $cond: [{ $eq: ["$power", "off"] }, "$time", null] },
            window: { documents: [1, "unbounded"] }
         }
      }
   }
}

it will take time@id: 2 - time@id: 1

Note, $dateDiff does not return fractional values. You may use unit: "minute" and divide result by 60.