I would like to determine the number of times that a value has crossed a threshold in either direction in a specified time frame.
As an example, lets say that I am interested in the number of times that an engine has started or stopped. Engine oil pressure is recorded in my table. When this value is < 5, I say that the engine is stopped. When it is > 15, I say that the engine is running. In between those values is a "deadzone"
In the time frame below, the engine has started 3 times and stopped three times.
+-------------+-------------+
| timestamp | EngineOil_P |
+-------------+-------------+
| 01-01 00:09 | 0.0 |
| 01-01 00:10 | 0.0 |
| 01-01 00:11 | 4.9 |
| 01-01 00:12 | 7.0 |
| 01-01 00:13 | 12.0 |
| 01-01 00:14 | 50.0 | // START!
| 01-01 00:15 | 50.0 |
| 01-01 00:16 | 25.0 |
| 01-01 00:17 | 15.5 |
| 01-01 00:18 | 9.0 |
| 01-01 00:19 | 0.3 | // STOP!
| 01-01 00:20 | 0.3 |
| 01-01 00:21 | 0.0 |
| 01-01 00:22 | 8.0 |
| 01-01 00:23 | 50.0 | // START!
| 01-01 00:24 | 50.0 |
| 01-01 00:25 | 50.0 |
| 01-01 00:26 | 50.0 |
| 01-01 00:27 | 18.0 |
| 01-01 00:28 | 0.0 | // STOP!
| 01-01 00:29 | 0.3 |
| 01-01 00:30 | 6.0 | -
| 01-01 00:31 | 8.0 | - careful!
| 01-01 00:32 | 4.0 |
| 01-01 00:33 | 0.0 |
| 01-01 00:34 | 25.0 | // START!
| 01-01 00:35 | 12.0 | -
| 01-01 00:36 | 9.0 | - careful!
| 01-01 00:37 | 23.0 |
| 01-01 00:38 | 0.0 | // STOP!
+-------------+-------------+
How might I get a result like starts = 3, stops = 3
?
The question is tricky because of the deadzone in between 5
and 15
.
The first curveball is at 00:29
to 00:33
, where the value jumps above 5, but NOT above 15, so that doesn't count as an engine start.
The second curveball is at 00:35
to 00:36
, where the value drops below the upper threshold but not below the lower threshold. Here, it would not count as an engine stop
I am using MySQL
(Mariadb 10
)