1

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)

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'.

| improve this answer | |
0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.