Geek Answers Handbook

MySQL query to get the total duration

I cannot solve this query problem to get the total duration of two different IP addresses, in which both are in the OFF state.

Cases in the sample data below

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

2 (Problem) - the IP address "10.0.1.2" is disabled during 13:00:00 to 13:25:00, and if we check it with a different IP address, it is disabled for 12:55:00 to 13: 20: 00. Thus, the total duration in both cases is 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
+5
source share
2 answers

Here is the starter for you.

  • I reduced the IP address to int for readability, ip .
  • I changed the status to text. It should be logical, and if MySQL does not have this, then it is possible char (1) or int with a CHECK constraint.
  • Do you need to consider some restrictions or a unique index to ensure that the status switches and prevents it from turning on when it is already turned on (turning it on multiple times)?
  • Declare appropriate indexes to speed up the query. Otherwise, it is 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 are using Common Table Expressions CTE in MySQL (you also did not specify a version, among other things).

If you do not have a CTE, just copy and replace all the links 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;

What 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 the minimum and maximum time, 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 obtain

  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 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 internal selection with LIMIT 1 consider the index on (ip, status, ts) .

For the connection, perhaps the index in ts can be used by your DBMS. The CTE ( WITH ) clause will only implement a virtual table. This may not apply to copying a CTE insert several times (twice here).

This should be a rude starter for you. This is far from an ideal solution. There may be other better ones.

+5
source

One way is to get the time in seconds using TIME_TO_SEC() and calculate the difference in the 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
+2
source

More articles:


All Articles