Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

closed as off-topic by JNK Jun 27 '13 at 12:11

If this question can be reworded to fit the rules in the help center, please edit the question.

    
How is it not working? Syntax error? Wrong results? Please also edit your question and post the actual query you used. Column names do not match between the SQL code you have given and the table description. –  ypercube Jun 26 '13 at 14:40
    
Actually I took above SQL Query as sample for my problem. I treat employeeId as motor_id here and employeeLogTime as timestamp and employeeLogType as runningstatus –  user2100659 Jun 26 '13 at 14:46
1  
Not in comments, edit the question. –  ypercube Jun 26 '13 at 14:51
    
after executing above query I'm getting error like: "Notice: Undefined index: ontime in D:\xampp\htdocs\motors\motors.php on line 345" and "Notice: Undefined index: offtime in D:\xampp\htdocs\motors\motors.php on line 346" –  user2100659 Jun 26 '13 at 14:53

2 Answers 2

up vote 1 down vote accepted

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 > 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 > 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 > 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
share|improve this answer
    
Thanks for your replay and here I'm giving SQL table URL: sqlfiddle.com/#!2/e5ed4/21 . In that url you can see the motor log table. actually individual users can have no. of motors in ponds based on his req. and he will turn on/off when ever he needs and there is no particular time to on/off. when ever he switch on/off the motor,data will save in log table. and we're showing the user how many hours motor run and how many hours OFF in this date(today) and like wise weekly(how many Hrs ON/OFF each day). –  user2100659 Jun 27 '13 at 4:38
    
Here I'm attaching how I'm capturing ON/OFF status in database: 54.251.45.227/aerators/index.php –  user2100659 Jun 27 '13 at 5:39
    
and output formate should be like: To Day Status:Aerator 1 10:00Hrs ON 14:00Hrs OFF,Aerator 2 15:00Hrs ON 09:00Hrs OFF and and so on. and For Weekly data:Aerator 1=> 2013-06-26 10:00Hrs ON 14:00Hrs OFF, 2013-06-27 15:00Hrs ON 09:00Hrs OFF and so on –  user2100659 Jun 27 '13 at 6:06
    
Thank you Mike, I got solution but I'm unable to calculate total elapsed time of each motor for instance motor_id 2 has two elapsed times right so how could i sum those two elapsed values as total elapsed time. Help me please –  user2100659 Jun 27 '13 at 10:30
    
@user2100659: Updated answer. –  Mike Sherrill 'Cat Recall' Jun 27 '13 at 12:50

Please try This, it returns differences in UNIXTIMESTAMP

SELECT m1.motor_id, (

SELECT SUM( UNIX_TIMESTAMP(timestamp ))
FROM motor_status_log as m2
WHERE runningstatus = "on" AND m2.motor_id = m1.motor_id
) AS On_Time, (

SELECT SUM( UNIX_TIMESTAMP(timestamp ))
FROM motor_status_log as m3
WHERE runningstatus = "off" AND m3.motor_id = m1.motor_id
) AS Off_Time
FROM `motor_status_log` as m1
GROUP BY `motor_id`
share|improve this answer
    
Thanks for your replay and here I'm giving SQL table URL: sqlfiddle.com/#!2/e5ed4/21 . In that url you can see the motor log table. actually individual users can have no. of motors in ponds based on his req. and he will turn on/off when ever he needs and there is no particular time to on/off. when ever he switch on/off the motor,data will save in log table. and we're showing the user how many hours motor run and how many hours OFF in this date(today) and like wise weekly(how many Hrs ON/OFF each day). –  user2100659 Jun 27 '13 at 7:26
    
Here I'm attaching how I'm capturing ON/OFF status in database: 54.251.45.227/aerators/index.php –  user2100659 Jun 27 '13 at 7:27
    
and output formate should be like: To Day Status:Aerator 1 10:00Hrs ON 14:00Hrs OFF,Aerator 2 15:00Hrs ON 09:00Hrs OFF and and so on. and For Weekly data:Aerator 1=> 2013-06-26 10:00Hrs ON 14:00Hrs OFF, 2013-06-27 15:00Hrs ON 09:00Hrs OFF and so on –  user2100659 Jun 27 '13 at 7:27

Not the answer you're looking for? Browse other questions tagged or ask your own question.