How to calculate total ON hours and Total OFF hours in day of a motor using PHP and MySQL In my application I would need to calculate total ON hrs and OFF hrs in a day of motors in one tank. Every time he/she turn ON/OFF motors, data will go and store in motor_status_log table of my database. 

While I searched for same thing in google I got following URL: calculating total login-logout time of a particular user in mysql

My rearranged query is:

"SELECT ilv.motor_id,SUM(UNIX_TIMESTAMP(offtime)-UNIX_TIMESTAMP(ontime)) FROM (SELECT a.motor_id, a.timestamp AS offtime,(SELECT MAX(b.timestamp) FROM motor_status_log b WHERE b.motor_id=a.motor_id AND b.timestamp&lt;a.timestamp AND b.runningstatus='on') as ontime FROM motor_status_log a WHERE a.runningstatus='off' AND a.timestamp BETWEEN '2013-06-26 00:00:00' AND '2013-06-26 23:59:59' AND motor_id='$motor_id') ilv GROUP BY ilv.motor_id"


and by using the given SQL Query in the above link it is not working. 

And table in my database is like:

+--------+-------+---------+--------------------+-----------------+
|user_id |Tank_id|motor_id |  timestamp         | runningstatus   |
+--------+-------+---------+--------------------+-----------------+
|    1   |    1  |    1    |2013-06-26 09:53:29 | off             |
|    1   |    1  |    1    |2013-06-26 10:45:42 | on              |
|    1   |    1  |    1    |2013-06-26 10:55:29 | off             |
|    1   |    1  |    1    |2013-06-26 17:55:39 | on              |
|    1   |    2  |    2    |2013-06-26 17:56:39 | off             |
|    1   |    2  |    2    |2013-06-26 08:53:29 | off             |
|    1   |    2  |    2    |2013-06-26 10:45:42 | on              |
|    1   |    2  |    2    |2013-06-26 12:55:29 | off             |
|    1   |    2  |    2    |2013-06-26 14:55:39 | on              |
|    1   |    3  |    3    |2013-06-26 17:56:39 | off             |
+----------------+---------+--------------------+-----------------+


Please help me. Thanks in advance
A table and some data. I ignored your primary key here, but I wouldn't do that in production.

CREATE TABLE motor_status_log (
  user_id integer not null,
  tank_id integer not null,
  motor_id integer not null,
  timestamp datetime not null,
  runningstatus varchar(3))
;

INSERT INTO motor_status_log VALUES
    (1, 1, 1, '2013-06-26 09:53:29', 'off'),
    (1, 1, 1, '2013-06-26 10:45:42', 'on'),
    (1, 1, 1, '2013-06-26 10:55:29', 'off'),
    (1, 1, 1, '2013-06-26 17:55:39', 'on'),
    (1, 2, 2, '2013-06-26 17:56:39', 'off'),
    (1, 2, 2, '2013-06-26 08:53:29', 'off'),
    (1, 2, 2, '2013-06-26 10:45:42', 'on'),
    (1, 2, 2, '2013-06-26 12:55:29', 'off'),
    (1, 2, 2, '2013-06-26 14:55:39', 'on'),
    (1, 3, 3, '2013-06-26 17:56:39', 'off');


You can get the basic data you need with this query. You probably want to take a hard look at the subquery that starts on the second line, and returns the column "run_end".

select t1.tank_id, t1.motor_id, t1.timestamp as run_start
  , (select min(timestamp) 
     from motor_status_log t2 
     where t2.tank_id = t1.tank_id
       and t2.motor_id = t1.motor_id
       and t2.timestamp &gt; t1.timestamp
       and t2.runningstatus = 'off') as run_end
from motor_status_log t1
where t1.runningstatus = 'on'
order by t1.tank_id, t1.motor_id, t1.timestamp;


You can use that in a FROM clause, and calculate the minutes of run time with timestampdiff().

select *, timestampdiff(minute, run_start, run_end) elapsed_min
from 
  (select t1.tank_id, t1.motor_id, t1.timestamp as run_start
    , (select min(timestamp) 
       from motor_status_log t2 
       where t2.tank_id = t1.tank_id
         and t2.motor_id = t1.motor_id
         and t2.timestamp &gt; t1.timestamp
         and t2.runningstatus = 'off') as run_end
  from motor_status_log t1
  where t1.runningstatus = 'on') t
order by tank_id, motor_id, run_start

TANK_ID  MOTOR_ID  RUN_START                   RUN_END                     ELAPSED_MIN
1        1         June 26 2013 10:45:42+0000  June 26 2013 10:55:29+0000  9
1        1         June 26 2013 17:55:39+0000  (null)                      (null)
2        2         June 26 2013 10:45:42+0000  June 26 2013 12:55:29+0000  129
2        2         June 26 2013 14:55:39+0000  June 26 2013 17:56:39+0000  181


I'd presume that the 'off' time for each day would be 24 hours minus the run time. I could be wrong.

You need to watch for 'on's that don't have corresponding 'off's, and vice-versa. (Motor 3 has an 'off' with no corresponding 'on'.) It's not clear what you might want to do about that kind of situation.  You could 


ignore it,
treat it as a maximal run period (which would probably guarantee that preventive maintenance would get done earlier rather than later),
treat it as an average run period, or
something else I haven't imagined yet.


I ignored it.



Summing elapsed time

To sum elapsed time the simplest way, wrap the previous query in another select. But this "simplest way" can give you misleading numbers if you have motors that are on over a period of days. (You can solve that problem with an auxiliary table, though.)

select motor_id
       , date(run_start) as start_date
       , sum(elapsed_min)/60.0 as hrs_on
       , 24 - sum(elapsed_min)/60.0 as hrs_off
from 
  (select *, timestampdiff(minute, run_start, run_end) elapsed_min
   from 
     (select t1.tank_id, t1.motor_id, t1.timestamp as run_start
       , (select min(timestamp) 
          from motor_status_log t2 
          where t2.tank_id = t1.tank_id
             and t2.motor_id = t1.motor_id
             and t2.timestamp &gt; t1.timestamp
             and t2.runningstatus = 'off') as run_end
    from motor_status_log t1
    where t1.runningstatus = 'on') t
  ) tt
group by motor_id, start_date
order by motor_id, start_date

motor_id  start_date  hrs_on  hrs_off
--
1         2013-06-26  0.15    23.85
2         2013-06-26  5.17    18.83