thank you for having time to read my question.

I have a mysql table that logs ON and OFF actions for a sensor each in a row. Is it posible to calculate time between OFF and ON when a new row is added with value ON?

This is my table

Is this posible? Thanks for your time.

CodePudding user response：

You can use the LAG() window function if you are using MySQL 8.0:

mysql> select *, timediff(time, prev) as diff 
  from (
    select *, lag(time) over (partition by sensor order by time) as prev
    from mytable
  ) as t;
 ---- ---------- -------- -------- ---------- ---------- 
| id | time     | action | sensor | prev     | diff     |
 ---- ---------- -------- -------- ---------- ---------- 
|  1 | 14:06:00 | OFF    |      1 | NULL     | NULL     |
|  5 | 17:58:00 | ON     |      1 | 14:06:00 | 03:52:00 |
|  6 | 18:02:00 | OFF    |      1 | 17:58:00 | 00:04:00 |
|  7 | 18:14:00 | ON     |      1 | 18:02:00 | 00:12:00 |
|  2 | 14:06:00 | OFF    |      2 | NULL     | NULL     |
|  4 | 16:41:00 | ON     |      2 | 14:06:00 | 02:35:00 |
|  3 | 15:32:00 | OFF    |      3 | NULL     | NULL     |
 ---- ---------- -------- -------- ---------- ---------- 

CodePudding user response：

Assume that there's no double OFF or ON occur consequently for each sensor, each sensor will have state OFF - ON - OFF - ON.

You can use the LEAD(), or LAG() windows function to access to the next/previous time occur on the partition sensor id.

SELECT sensor_stat.*,
-- access the "next" value of record (since we're order by time in descending order, so it's the previous time that there's an action occur for this sensor) 
LEAD(time) OVER (PARTITION BY sensor ORDER BY time DESC) as last_action_occur_on
FROM sensor_stat
ORDER BY sensor_stat.time DESC;

give you

Now you only need to focus on the row with action ON, the last_action_occur_on will be the time of the OFF action occur on this sensor, simply use TIMEDIFF(last_action_occur_on, time) to get the duration between the last OFF timestamp and this ON timestamp.

Read more:
MySQL Lead function
MySQL TIMEDIFF function