MySQL query to get common duration

Question

I am unable to solve this query problem for fetching common duration of two different IP addresses in which both are in OFF status.

Cases in the below sample data

1 (Simple case) - IP address "10.0.1.2" is remains OFF for 00:10:10 to 00:20:00 and "10.0.1.3" is also OFF in this duration, so the common duration of OFF for both is 00:10:10 to 00:20:00.

2 (Problematic)- IP address "10.0.1.2" is OFF for 13:00:00 to 13:25:00 and if we check it with other IP address, it is OFF for 12:55:00 to 13:20:00. So, the common duration in both are 13:00:00 to 13:20:00.

Sample data:

ID   IP address  Status   Time
----------------------------------
1    10.0.1.2    OFF      00:10:00
1    10.0.1.2    ON       00:20:00
1    10.0.1.2    OFF      11:00:00
1    10.0.1.2    ON       11:20:00
1    10.0.1.2    OFF      13:00:00
1    10.0.1.2    ON       13:25:00
1    10.0.1.2    OFF      14:05:00
1    10.0.1.2    ON       14:10:00
1    10.0.1.2    OFF      15:35:00
1    10.0.1.2    ON       15:45:00
1    10.0.1.3    OFF      00:10:00
1    10.0.1.3    ON       00:20:00
1    10.0.1.3    OFF      11:05:00
1    10.0.1.3    ON       11:25:00
1    10.0.1.3    OFF      12:55:00
1    10.0.1.3    ON       13:20:00
1    10.0.1.3    OFF      17:10:00
1    10.0.1.3    ON       17:15:00
1    10.0.1.3    OFF      15:00:00
1    10.0.1.3    ON       16:45:00

Output:

ID   IP addresses       Status  Time
-----------------------------------------
1    10.0.1.3,10.0.1.2  OFF      00:10:00
1    10.0.1.3,10.0.1.2  ON       00:20:00
1    10.0.1.3,10.0.1.2  OFF      11:05:00
1    10.0.1.3,10.0.1.2  ON       11:20:00
1    10.0.1.3,10.0.1.2  OFF      13:00:00
1    10.0.1.3,10.0.1.2  ON       13:20:00
1    10.0.1.3,10.0.1.2  OFF      15:35:00
1    10.0.1.3,10.0.1.2  ON       15:45:00

Answer 1

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.

Answer 2

One method is to get time as seconds using TIME_TO_SEC() and calculate the difference in a stored procedure:

Create table common_duration (
ip varchar (10),
start_time time,
end_time time
)

CREATE PROCEDURE `comm_time`()
    BEGIN
    DECLARE curs1 CURSOR FOR SELECT `IP`, TIME_TO_SEC(`time`) as time, STATUS FROM TABLE;
    DECLARE ip varchar(20);
    DECLARE iptime time;
    DECLARE ipstime time;
    DECLARE ipstatus varchar(10);
    OPEN curs1;
    FETCH curs1 INTO ip,iptime,ipstatus;
    if (status='ON')
    insert into `common_duration`(ip, start_time, end_time) values(ip, ipstime, iptime);
    else
    ipstime=iptime;
    endif;
    CLOSE curs1;
    SELECT t1.ip SEC_TO_TIME(t1.end_time-t1.start_time) as time_duration FROM `common_duration t1, `common_duration t2
    WHERE t1.time_duration= t2.time_duration
          AND t1.ip != t2.ip;
    End