In this previous question, I asked for a way of identifying some gaps and islands. with a table t
below;
+---------------------+------------+---------------+
| ts | EngOilP_sd | CompOilLVL_sd |
+---------------------+------------+---------------+
| 2015-06-24 20:28:07 | 0 | 0 |
| 2015-06-24 20:30:20 | 1 | 0 |
| 2015-06-24 20:36:47 | 1 | 0 |
| 2015-06-24 20:41:11 | 1 | 0 |
| 2015-06-24 20:43:29 | 1 | 0 |
| 2015-06-24 20:45:42 | 0 | 0 |
| 2015-06-24 20:47:51 | 0 | 0 |
| 2015-06-24 20:49:59 | 0 | 1 |
| 2015-06-24 20:52:01 | 0 | 1 |
| 2015-06-24 20:54:17 | 0 | 0 |
+---------------------+------------+---------------+
The following view will give me the islands of data where values are successively true
;
create view t_pivot as
select 'EngOilP_sd' as sd, ts, EngOilP_sd as val from t
union all
select 'CompOilLVL_sd', ts, CompOilLVL_sd from t;
# explain
select sd, timediff(stop, start) as duration, start, stop
from (
select x.sd, x.start, min(case y.val when 0 then y.ts end) as stop
from (
select sd, min(case val when 1 then ts end) as start
from t_pivot
group by sd
) as x
join t_pivot as y
on x.sd = y.sd
and y.ts > x.start
group by x.sd, x.start
) as z
order by sd desc;
Gives
+---------------+----------+---------------------+---------------------+
| sd | duration | start | stop |
+---------------+----------+---------------------+---------------------+
| EngOilP_sd | 00:15:22 | 2015-06-24 20:30:20 | 2015-06-24 20:45:42 |
| CompOilLVL_sd | 00:04:18 | 2015-06-24 20:49:59 | 2015-06-24 20:54:17 |
+---------------+----------+---------------------+---------------------+
2 rows in set (0.00 sec)
Now, I am stumped as how to look at the following table for n gaps
+---------------------+------------+---------------+---------------+
| ts | EngOilP_sd | CompOilLVL_sd | Brake_sd |
+---------------------+------------+---------------+---------------+
| 2015-06-24 20:28:07 | 0 | 0 | 0 |
| 2015-06-24 20:30:20 | 1 | 0 | 0 |
| 2015-06-24 20:36:47 | 1 | 1 | 0 |
| 2015-06-24 20:41:11 | 1 | 1 | 1 |
| 2015-06-24 20:43:29 | 1 | 1 | 1 |
| 2015-06-24 20:45:42 | 1 | 0 | 1 |
| 2015-06-24 20:47:51 | 0 | 0 | 1 |
| 2015-06-24 20:49:59 | 0 | 1 | 0 |
| 2015-06-24 20:52:01 | 1 | 1 | 0 |
| 2015-06-24 20:53:01 | 1 | 1 | 0 |
| 2015-06-24 20:54:01 | 1 | 1 | 1 |
| 2015-06-24 20:56:01 | 1 | 1 | 1 |
| 2015-06-24 20:57:01 | 1 | 1 | 1 |
| 2015-06-24 20:58:01 | 1 | 1 | 1 |
| 2015-06-24 21:02:01 | 1 | 1 | 1 |
| 2015-06-24 21:32:01 | 0 | 1 | 1 |
| 2015-06-24 21:52:01 | 0 | 0 | 1 |
| 2015-06-24 22:12:01 | 0 | 0 | 0 |
| 2015-06-24 22:20:01 | 0 | 0 | 0 |
| 2015-06-24 22:49:01 | 0 | 1 | 1 |
| 2015-06-24 23:52:01 | 0 | 1 | 1 |
| 2015-06-24 23:54:17 | 0 | 0 | 0 |
+---------------------+------------+---------------+---------------+
The result set I am looking for here would be as below
+---------------+----------+---------------------+---------------------+
| sd | duration | start | stop |
+---------------+----------+---------------------+---------------------+
| EngOilP_sd | 00:15:22 | 2015-06-24 20:30:20 | 2015-06-24 20:45:42 |
| EngOilP_sd | 00:10:00 | 2015-06-24 20:52:20 | 2015-06-24 21:02:42 |
| CompOilLVL_sd | 00:06:42 | 2015-06-24 20:36:47 | 2015-06-24 20:43:29 |
| CompOilLVL_sd | 00:42:02 | 2015-06-24 20:49:59 | 2015-06-24 21:32:01 |
| CompOilLVL_sd | 00:03:00 | 2015-06-24 20:49:59 | 2015-06-24 20:54:17 |
| Brake_sd | 00:06:40 | 2015-06-24 20:41:11 | 2015-06-24 20:47:51 |
| Brake_sd | 00:58:01 | 2015-06-24 20:54:01 | 2015-06-24 21:52:01 |
| Brake_sd | 01:02:00 | 2015-06-24 22:49:01 | 2015-06-24 23:52:01 |
+---------------+----------+---------------------+---------------------+
8 rows in set (0.00 sec)
EDIT Initially, I did not have the column Brake_sd
in the question. User @tombom's answer correctly describes the case where the Brake_sd
column is not present, although that is the only difference