1

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:

enter image description here

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

  • 1
    What other information do you have available when making this query? You say you need to "calculate the duration in which the switch was on". Does that mean you know for sure the switch was on, and at least one time value for which it was on? Or do you want to be able to check an arbitrary time interval and get all durations of all on/off times... which then leads to questions about how to handle endpoints, etc. More information on what the desired output will look like will help greatly in anyone's attempt to answer your question. – Willem Renzema Jul 10 '18 at 19:49
  • @WillemRenzema I've updated the question. – iPath ツ Jul 10 '18 at 21:18
3

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.

  • I've updated the question and will attach Create/insert statements shortly. – iPath ツ Jul 10 '18 at 21:31
  • @iPathツ I added a few paragraphs, based on your modifications to the Question. – Rick James Jul 10 '18 at 22:27
  • I've tried windowing functions in MySQL 8 and the query runs within fractions of a second. In contrast the first approach takes more than 10 seconds for a period of a few days. I'm considering an upgrade to more recent MySQL version (may be MariaDB). But it will take time since I need to compile MySQL for ARM. – iPath ツ Jul 11 '18 at 18:14
  • Please share the MySQL 8 version of the query. – Rick James Jul 11 '18 at 18:43
  • 1
    Bravo! And it looks like the 2-step method is needed. That is, my Approach 4 will probably not work since you need the endtimes. – Rick James Jul 11 '18 at 22:47
0
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

  • Very clever solution! Unfortunately it takes about 2 minutes to compute the duration for 2 days period. Can you suggest some optimization in terms of indexes? – iPath ツ Jul 11 '18 at 18:13
  • @iPathツ CREATE INDEX idx ON feedsdata (value, FeedId, `DateTime`) optimizes the subquery. Outer query can not be optimized by index build. – Akina Jul 12 '18 at 4:15
  • @iPathツ 2-days period == how many records? how many output records? and how many different FeedId values? PS. I think the solution with user variables usage maybe more fast... – Akina Jul 12 '18 at 4:17
0

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.

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.