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:
- The on/off events are reported a few times during a period i.e. there are duplicates
- 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.
Best Answer
Approach 1 (works in any version)
ON a.seq = b.seq-1
to match up adjacent rows.WHERE a.Value != b.Value
The table will contain, for example:
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 usingORDER 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 beCREATE 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 simulateLAG()
.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". ThenSUM
the "on times".Details (Maybe I will give you details, but first...)
Provide sample data (not images) ready to run (
CREATEs
andINSERTs
). 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) isON
. 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.