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.