Question 116262 in Mongodb Question posted in
The official documentation can be found here.

MongoDb query to calculate time between alternating on off switch

{
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?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    In case someone in the future faces the same 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"],
                        },
                      ],
                    },
                  },
                },
              },
            },
          },
          
        ]);

  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search