0

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

Time Action Sensor Duration
14:06 OFF 001
14:06 OFF 002
15:32 OFF 003
16:41 ON 002 duration between OFF (14:06) and ON (16:41)
17:58 ON 001 duration between OFF (14:06) and ON (17:58)
18:02 OFF 001
18:14 ON 001 duration between OFF (18:02) and ON (18:14)

Is this posible? Thanks for your time.

Damien Dijkman
  • 1
  • 1

2 Answers2

0

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     |
+----+----------+--------+--------+----------+----------+
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
0

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

time action sensor last_action_occur_on
18:14:00 ON 001 18:02:00
18:02:00 OFF 001 17:58:00
17:58:00 ON 001 14:06:00
16:41:00 ON 002 14:06:00
15:32:00 OFF 003
14:06:00 OFF 001
14:06:00 OFF 002

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

ThangLeQuoc
  • 2,272
  • 2
  • 19
  • 30