MongoDb calculate the operational hours

Working with Data
1 
{
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?

2

Hi @Harsh_Bhudolia ,

Its a fairly complex query only managable by the aggregation framework.

It sounds like the best way is to either:

  1. Change the data model to store the working hours once the off event kicks and calculate the time on the fly from the last on state of that device.
  2. Get the times sorted from the database and calculate the working hours in the application logic.

If you still want to go the aggregation way I took the challenge and created the following aggregation, considering those source documents:

db.log.find()
{ _id: ObjectId("636b789f50f3507ba44a1905"),
  metadata: { eventcode: 100 },
  power: 'on',
  time: 1667984669 }
{ _id: ObjectId("636b79302571c4c174a3b231"),
  metadata: { eventcode: 100 },
  power: 'off',
  time: 1667987641 }
{ _id: ObjectId("636b794d2571c4c174a3b232"),
  metadata: { eventcode: 100 },
  power: 'on',
  time: 1667991241 }
{ _id: ObjectId("636b79742571c4c174a3b233"),
  metadata: { eventcode: 100 },
  power: 'off',
  time: 1667994841 }

Aggregation:

db.log.aggregate([{
 $sort: {
  time: 1
 }
}, {
 $facet: {
  on: [
   {
    $match: {
     power: 'on'
    }
   }
  ],
  off: [
   {
    $match: {
     power: 'off'
    }
   }
  ]
 }
}, {
 $project: {
  powerHours: {
   $sum: {
    $map: {
     input: {
      $range: [
       0,
       {
        $size: '$on'
       }
      ]
     },
     'in': {
      $subtract: [
       {
        $arrayElemAt: [
         '$off.time',
         '$$this'
        ]
       },
       {
        $arrayElemAt: [
         '$on.time',
         '$$this'
        ]
       }
      ]
     }
    }
   }
  }
 }
}])
{ powerHours: 6572 }

As you can see its fairly complex, it uses a facet to get all the “on” in one array and “off” in another sorted and subtract “off” from its “om” one from the other and then sums .

Thanks
Pavel

3

Thanks man, if I ever meet you I should treat you for just helping me in times

4

One Question, input should have an array but here you have given it a range and not an array, also you directly taken the difference of uix timestamp does it work?

5

Hi @Harsh_Bhudolia ,

Well on.time is an element that exists in each array element so it is array of times…

As far as I know uix timestamp is the number of seconds since 1970 so the subtraction of 2 timestamps will be the number of seconds between them…

7

number of milliseconds is unix timestamp

8

after facet we get two arrays, on and off which are array of objects then we are mapping it and as an input you are only taking range from o to size of on array, till here it makes sense to me, then in operation for each element in mongodb you are subtracting that also makes sense to me,
What I do not understand it how $off.time resolves to array of times, because if after facet if I try project to display off.time it displays nothing

9

so i checked again and yes $off.time and $on.time is resolving to an array, so thanks for the query

1 Like