Interested in counting number of threshold crossings

Question

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)

Answer 1

See if this meets your specs:

SELECT SUM(turned_on), SUM(turned_off)
    FROM (
        SELECT
                ( @prev <=  5 AND EngineOil_P >  5 ) turned_on,
                ( @prev >= 15 AND EngineOil_P < 15 ) turned_off,
                @prev := EngineOil_P
            FROM ( SELECT @prev := 0 ) init
            JOIN tbl
            ORDER BY timestamp
         ) x;

Note that boolean expressions evaluate to 0 (false) or 1 (true). Hence the SUM() gets what you want.

There could be a problem with the initial state -- if the first row has > 15, it will be treated as a 'turn on'.

Answer 2

I believe the key here lies in comparing the previous for every new row with the current value. I don't know MySQL syntax but perhaps this pseudo code helps convey the logic I'm thinking of:

if (previous_value < 15 and current_value >= 15)

{ set start_count += 1 }

if (previous_value > 5 and current_Value <= 5)

{ set stop_count += 1 }

I'm not sure I understand the trickiness in the "deadzone" though.