MySQL query to get common duration

mysql, sql

Solution

Here's a starter for you.

- I reduced the IP address to an int for readability, `ip`.

- I changed status to text. It should be boolean and if MySQL does not have that, then maybe char(1) or int with CHECK constraint.

- You need to consider some constraint or unique index to guarantee status switching and preventing turning it on while it is already on (turning it on multiple times)?

- Declare appropriate indices to speed up the query. Otherwise it's quadratic complexity.

CREATE TABLE foo (ip int NOT NULL, status text NOT NULL,
    ts time NOT NULL, PRIMARY KEY (ip, status, ts));

INSERT INTO foo VALUES
(2, 'OFF', '00:10:00'),
(2, 'ON',  '00:20:00'),
(2, 'OFF', '11:00:00'),
(2, 'ON',  '11:20:00'),
(2, 'OFF', '13:00:00'),
(2, 'ON',  '13:25:00'),
(2, 'OFF', '14:05:00'),
(2, 'ON',  '14:10:00'),
(2, 'OFF', '15:35:00'),
(2, 'ON',  '15:45:00'),
(3, 'OFF', '00:10:00'),
(3, 'ON',  '00:20:00'),
(3, 'OFF', '11:05:00'),
(3, 'ON',  '11:25:00'),
(3, 'OFF', '12:55:00'),
(3, 'ON',  '13:20:00'),
(3, 'OFF', '17:10:00'),
(3, 'ON',  '17:15:00'),
(3, 'OFF', '15:00:00'),
(3, 'ON',  '16:45:00');

Assuming you have Common Table Expressions CTE's in MySQL (you didn't specify the version either, among other things).

If you don't have CTEs, then just copy and replace all references to the CTE (`off` in this case) and give it a name. The last example will not use `WITH`.

WITH off AS
(SELECT ip,
        ts "off_from",
        (SELECT ts FROM foo
         WHERE ip = a.ip AND a.ts <= ts AND status = 'ON'
         ORDER BY ts ASC LIMIT 1) "off_until"
 FROM foo a WHERE status = 'OFF'
)
SELECT * FROM off;

Which gives

 ip | off_from | off_until
----+----------+-----------
  2 | 00:10:00 | 00:20:00
  2 | 11:00:00 | 11:20:00
  2 | 13:00:00 | 13:25:00
  2 | 14:05:00 | 14:10:00
  2 | 15:35:00 | 15:45:00
  3 | 00:10:00 | 00:20:00
  3 | 11:05:00 | 11:25:00
  3 | 12:55:00 | 13:20:00
  3 | 17:10:00 | 17:15:00
  3 | 15:00:00 | 16:45:00

WITH off AS
(SELECT ip,
        ts "off_from",
        (SELECT ts FROM foo
         WHERE ip = a.ip AND a.ts <= ts AND status = 'ON'
         ORDER BY ts ASC LIMIT 1) "off_until"
 FROM foo a WHERE status = 'OFF'
)
SELECT *
FROM off x
INNER JOIN off y
ON  x.off_from <= y.off_from AND y.off_from < x.off_until
AND x.ip <> y.ip ;

 ip | off_from | off_until | ip | off_from | off_until
----+----------+-----------+----+----------+-----------
  2 | 00:10:00 | 00:20:00  |  3 | 00:10:00 | 00:20:00
  2 | 11:00:00 | 11:20:00  |  3 | 11:05:00 | 11:25:00
  3 | 00:10:00 | 00:20:00  |  2 | 00:10:00 | 00:20:00
  3 | 12:55:00 | 13:20:00  |  2 | 13:00:00 | 13:25:00
  3 | 15:00:00 | 16:45:00  |  2 | 15:35:00 | 15:45:00

And to get take the minimum and maximum of the times use

WITH off AS
(SELECT ip,
        ts "off_from",
        (SELECT ts FROM foo
         WHERE ip = a.ip AND a.ts <= ts AND status = 'ON'
         ORDER BY ts ASC LIMIT 1) "off_until"
 FROM foo a WHERE status = 'OFF'
)
SELECT x.ip "ip_a", y.ip "ip_b",
       greatest( x.off_from, y.off_from ) "off_from",
       least( x.off_until, y.off_until ) "off_until"
FROM off x
INNER JOIN off y
ON  x.off_from <= y.off_from AND y.off_from < x.off_until
AND x.ip <> y.ip ;

to yield

 ip_a | ip_b | off_from | off_until
------+------+----------+-----------
    2 |    3 | 00:10:00 | 00:20:00
    2 |    3 | 11:05:00 | 11:20:00
    3 |    2 | 00:10:00 | 00:20:00
    3 |    2 | 13:00:00 | 13:20:00
    3 |    2 | 15:35:00 | 15:45:00

Without `WITH` (copy paste and name the CTE).

SELECT x.ip "ip_a", y.ip "ip_b",
       greatest( x.off_from, y.off_from ) "off_from",
       least( x.off_until, y.off_until ) "off_until"
FROM
(SELECT ip,
        ts "off_from",
        (SELECT ts
         FROM foo
         WHERE ip = a.ip AND a.ts <= ts AND status = 'ON'
         ORDER BY ts ASC LIMIT 1) "off_until"
 FROM foo a WHERE status = 'OFF'
) x
INNER JOIN
(SELECT ip,
        ts "off_from",
        (SELECT ts
         FROM foo
         WHERE ip = a.ip AND a.ts <= ts AND status = 'ON'
         ORDER BY ts ASC LIMIT 1) "off_until"
 FROM foo a WHERE status = 'OFF'
) y
ON  x.off_from <= y.off_from
AND y.off_from < x.off_until
AND x.ip <> y.ip ;

For the inner select with `LIMIT 1` consider an index on `(ip, status, ts)`.

For the join, maybe an index on `ts` can be used by your DBMS. The CTE (`WITH` clause) would materialize the virtual table once only. That may not apply with copy-pasting the CTE several times (two times here).

This is supposed to be a rough starter for you. It's by far not perfect or the best solution. There may be other better ones.

Problem

Original source