-1

I'm looking for a way to get the total time between two alternating events in MYSQL.

I'm using a MYSQL database to store events when a door opens/closes. I'm looking for a way to visualise the total time a door was open each hour.

I'm using datareportive to visualize my data, currently I'm able to show how many times the door opened each hour by the query below: 

SELECT DATE_FORMAT(DATE_ADD(date, INTERVAL 30 MINUTE),'%Y-%m-%d %H:00:00') as hour,
COUNT(*) as num_rows 
FROM events 
GROUP BY hour;

The database looks like: 

ID  door                    Action  date

13  my unique door name!    Open    2019-09-09T09:01:12
14  my unique door name!    Close   2019-09-09T09:01:23
15  my unique door name!    Open    2019-09-09T09:01:30
16  my unique door name!    Close   2019-09-09T09:01:35
17  my unique door name!    Open    2019-09-09T09:01:37
18  my unique door name!    Close   2019-09-09T09:01:40
19  my unique door name!    Open    2019-09-09T09:01:50

I'm looking for a way the query returns the total time the door was open each hour.

There is one big catch: sometimes the events Open/Close don't alternate correctly, for example power outage on the doorsensor could result in two times the same event listed in the database.

SQLfiddle

Stinosko
  • 3
  • 3
  • 1
    Possible duplicate of [Query to calculate average time between successive events](https://stackoverflow.com/questions/1946916/query-to-calculate-average-time-between-successive-events) – Bulat Sep 28 '19 at 18:30
  • Saw it before but I couldn't find a solution based on the comments for MYSQL database. Possible because my SQL knowledge is quite limited. – Stinosko Sep 28 '19 at 18:34

1 Answers1

0

It is difficult to suggest something specific without http://sqlfiddle.com/ example. so I can just give you a strategy.

  1. create an INTEGER column with 1/0 to represent Open/Close events.
  2. Use SUM(action_int) OVER (see example here Calculate a running total in MySQL) to calculate running total of opens and closes which will give you unique identifier for each full combination.
  3. Self join this results based on this identifier filtering right side to Open, left side to Close
  4. Sum up the difference between close_date and open_date from previous step to calculate total time door was open.

This should take care of your orphan Open events. You probably will have to fiddle a bit with orphan Close events to exclude them from the dataset.

Here is sql for Postgres, I believe with minor adjustments you can make it work for MySql:

WITH door_events AS (
  SELECT 
    id, 
    door, 
    action, 
    SUM(action) OVER(PARTITION BY door ORDER BY date) AS cycle_id,
    date
  FROM 
      events 
)
SELECT 
  door, 
  SUM(date_close - date_open) door_open 
FROM
(
  SELECT 
   id as open_id,
   door as door,
   date as date_open,
   cycle_id
  FROM 
    door_events
  WHERE action = 1
) AS o LEFT JOIN
(
  SELECT 
    id as close_id,
    door as door_close,
    date as date_close,
    cycle_id
   FROM 
    door_events
   WHERE action = 0
) AS c ON o.cycle_id = c.cycle_id
GROUP BY door

http://sqlfiddle.com/#!17/bafb1/19/0

Bulat
  • 6,869
  • 1
  • 29
  • 52