1

I don't even know how to phrase the question properly but I am trying to calculate the total time a device is switched on. I have a mysql database which logs the time the device is either turned on or off.

an example of what my database looks like:

  device id             logtime           status
---------------------------------------------------
17x1p14e6662  April 12th 2017, 1:05:52 pm   ON
17x1p14e6662  April 12th 2017, 1:06:34 pm   OFF
17x1p14e6662  April 12th 2017, 1:07:02 pm   ON
17x1p14e6662  April 12th 2017, 1:14:00 pm   OFF
17x1p14e6662  April 12th 2017, 1:34:52 pm   ON
17x1p14e6662  April 12th 2017, 3:25:20 pm   OFF
17x1p14e6662  April 12th 2017, 5:30:00 pm   ON

I know how to calculate the difference between two time periods using momentJS, something like this (referenced from Get the time difference between two datetimes) :

var now  = "04/09/2013 15:00:00";
var then = "02/09/2013 14:20:30";

var ms = moment(now,"DD/MM/YYYY HH:mm:ss").diff(moment(then,"DD/MM/YYYY HH:mm:ss"));
var d = moment.duration(ms);
var s = Math.floor(d.asHours()) + moment.utc(ms).format(":mm:ss");

If I retrieve all records for a device, how can I only calculate the total times between each "ON" and "OFF" ?

Community
  • 1
  • 1
Bruno
  • 511
  • 2
  • 6
  • 19
  • Is for each 'ON' time also an 'OFF' time? – Janick Fischer Apr 12 '17 at 11:46
  • Yes, it always alternates, every "ON" will be followed by "OFF". – Bruno Apr 12 '17 at 11:47
  • So why don't you just make an array with all 'ON' and one with all 'OFF' times. Then loop over them and calculate the difference... – Janick Fischer Apr 12 '17 at 11:48
  • What if the number of records of "ON" and "OFF" aren't the same? If, for example, a device is still "ON", then there won't be an "OFF" record? Also, I am a beginner and I am not sure how to even start with that lol. – Bruno Apr 12 '17 at 11:50
  • That's what I was asking for in my first question... :) So there aren't the same numbers... the Question is what you want to do then. You could calculate the time till now or just stop calculating. That's your choice :) – Janick Fischer Apr 12 '17 at 11:54
  • Oh! I didn't think of using current time to calculate if device is still "ON" ! – Bruno Apr 12 '17 at 11:56

2 Answers2

0

As @JanickFischer wrote in your comments, you'd need to sum up the individual cycles. Since you didn't post any code on how you read your data / store your data in your arrays I'll offer this general workflow:

Step 1: Divide arrays by ON and OFF

Step 2: Compare lengths of arrays

  • 2a) if equal: device is currently off and you can just calculate the sum of the individual on-off cycles (eg (datetimeOff[0] - datetimeOn[0]) + (datetimeOff[1] - datetimeOn[1]) + (...), parenthesis just for readability)
  • 2b) if count(ONs) = count(OFFs) + 1: device is currently on, you could add the current datetime to your off array and use the same procedure as 2a
  • 2c) if count(ONs) > count(OFFs) + 1: Check your data for gaps
  • 2d) if count(OFFs) > count(ONs): Check your data for gaps
ccKep
  • 5,786
  • 19
  • 31
  • Thanks so much ccKep. I'll use the workflow you suggested, it gives me enough to go by :) Much appreciated. – Bruno Apr 12 '17 at 12:13
0

Steps

  1. You need 2 arrays. One with all the times 'ON' and one with all 'OFF' times.
  2. Loop over the 'ON' one.
  3. Check if there is a value on the index of the 'OFF' array.
  4. If yes, calculate the time and add it.
  5. If no, get the current time and do the calculation and adding.
  6. Finished :D
Janick Fischer
  • 651
  • 7
  • 17