Calculating duration between two events in a MySQL database (timeseries)

Question

I have a (timeseries like) table in MySql which stores some on/off events. These events are reported at random intervals and represent the current state of an IoT switch:

I need help building the SQL query to calculate the duration (in munites) in which the switch was on.

There are a few obstacles:

1. The on/off events are reported a few times during a period i.e. there are duplicates
2. MySQL is version 5.5 and cannot be upgraded (i.e. lacking windowing functions). This version does not support nesting of sub queries in views.

UPDATE My goal is to calculate the total time the switch was ON for a given period. For example: between 2018-07-01 00:00:00 and 2018-07-10 23:59:59 the switch was ON for 56 minutes. My idea was to have the "ON" duration intervals in a table like this:

date-time           | duration-in-munites
2018-07-10 13:47:27 | 47 
2018-07-11 00:01:13 | 12
...

Where date-time is when the first ON have occurred

From there I could sum up the durations for a given time span. This may not be the right way to do it so I'm open to suggestions how to do it.

• I'm interested only on OFF to ON transition time and duration

• The initial values are unknown. The switch may have reported a few "off" events before reporting "on" event

• Another issue is that the table contains data from other sources, so the IDs are no sequential.

• The MySql server runs on Raspberry PI 3. As far as I know there is no ARM version of MySQL 8.

Here are Create/Insert statements for sample data

Answer 1

Approach 1 (works in any version)

1. Add a sequence number to the table (or a copy of the columns of interest).
2. Do a "self join" ON a.seq = b.seq-1 to match up adjacent rows.
3. Filter down to the status changes: WHERE a.Value != b.Value

The table will contain, for example:

  3  13:44:13  off   4  13:47:27   on
 12  14:27:27   on  13  14:34:48  off

Assuming you want the timespan between pairs of rows in this table, again add a sequence number, and do the self join to extract and subtract the next to last column.

Approach 2 (plug for upgrading)

If you have MariaDB 10.2 or MySQL 8.0, then there are "Windowing functions" that make this task immensely simpler. See LAG(); it obviates the need for the self joins and the sequencing.

In the first pass, look for LAG(Value) != Value while using ORDER BY DateTime. In the second pass, diff the LAG of the time with the current time.

Approach 3 (possibly the best for 5.5 without windowing)

Since you are stuck in the nearly-antique 5.5, Consider using a Stored Procedure, not a VIEW. It will involve two extra tables. They cannot be CREATE TEMPORARY TABLE since you can't use a temp table twice for the self-join.

So, a better approach (without windowing) would be to use @variables to simulate LAG().

Approach 4 (somewhere between 1 and 3)

This involves a single pass of looking at consecutive rows. If the first of a consecutive (based on datetime) pair of rows says ON, then calculate the "on time". Then SUM the "on times".

Details (Maybe I will give you details, but first...)

Provide sample data (not images) ready to run (CREATEs and INSERTs). Also provide expected output. (I understand ON to OFF, but what about OFF to ON; and do you want the start time of the range; etc.)

A hiccup

Since you want the "on time" to stop at the top of the hour, extra code is needed to stop the summation, etc. One artificial way to do this is to add 2 rows, one second apart at the top of each hour. The first is OFF, the second (1 second later) is ON. This will create a 1-second error in the results, but has no impact on the Approaches except for simplifying the "top of hour" requirement.

Answer 2

SELECT FeedId, MIN(dt_start) dt_start, dt_end, TIMEDIFF(dt_end, MIN(dt_start)) duration
FROM ( SELECT t1.FeedId, t1.`DateTime` dt_start, t2.`DateTime` dt_end
       FROM feedsdata t1, feedsdata t2
       WHERE t1.value = 'on'
         AND t2.value = 'off'
         AND t1.FeedId = t2.FeedId
         AND t1.`DateTime` < t2.`DateTime`
         AND NOT EXISTS (SELECT 1
                         FROM feedsdata
                         WHERE value = 'off'
                           AND t1.`DateTime` < `DateTime`
                           AND `DateTime` < t2.`DateTime`
                           AND FeedId = t1.FeedId)
      ) dummy
GROUP BY FeedId, dt_end;

fiddle

Answer 3

The solution using user variables:

SELECT p_feed FeedId, MAX(p_start) dt_start, MAX(p_end) dt_end, TIMEDIFF(MAX(p_end), MAX(p_start)) duration 
FROM (
SELECT @start := CASE WHEN f.value = 'on' AND @value = 'off' THEN f.`datetime` ELSE null END p_start,
       @end   := CASE WHEN @value = 'on' AND f.value = 'off' AND @feed = f.FeedId THEN f.`datetime` ELSE null END p_end,
       @num   := @num + (@start IS NOT NULL) p_num,
       @feed  := f.FeedId p_feed,
       @value := f.value p_value
FROM feedsdata f, (SELECT @start := 0, @end := 0, @num := 0, @feed := 0, @value := '') init_vars
ORDER BY f.FeedId ASC, f.`DateTime` ASC
     ) subquery
WHERE p_num > 0
GROUP BY p_num
HAVING duration IS NOT NULL;

fiddle (with 2 different FeedId values).

Needs in index by (FeedId, `DateTime`) for optimization.