Implementation of removing overlapping time and calculating time difference in MySQL

Implementation of removing overlapping time and calculating time difference in MySQL

I personally do not recommend using stored procedures in actual development as it is full of inconveniences. The reason I wrote this is all for learning. If any experts see problems with my content, feel free to point them out or criticize me.

need:

In production, there is often a business that requires calculating the difference between two times, such as total downtime, total membership activation time, etc. . . However, these times are often not continuous, but intermittent and may even overlap. The time difference cannot be directly calculated.

For example:

drive:

At first, I thought of implementing it with a single SQL statement, for example:

SELECT TIMESTAMPDIFF(MINUTE, '2021-08-19 14:30:00', '2021-08-19 15:00:00') FROM DUAL;

I found that there are thousands of database data, and it is impossible to do this. It is also impossible to use UNION to splice them. If there is a lot of data, there will definitely be loops. Therefore, without using Java language, I chose to try to use stored procedures to solve the following problem.

Ideas:

First, the data that enters the loop once will not be calculated to prevent the subsequent data from overlapping with it.

Starting from the second piece of data, we need to determine whether the start time overlaps with the previous data. If it overlaps, we need to check whether the end time also overlaps. If it overlaps, we will do nothing. If it does not overlap, we will assign this value to the end time of the previous data.

If the start time is no longer within the range, then it is necessary to determine whether the start time is before or after the previous time.

If it is before this range, assign this value to the start time of the previous data.

After this range, calculate and assign

The last loop also needs to calculate and assign

accomplish:

First create a table and simulate data

CREATE TABLE test01 (
  id int(32) unsigned NOT NULL AUTO_INCREMENT,
  start_time datetime NOT NULL,
  end_time datetime NOT NULL,
  PRIMARY KEY (`id`)
) 
 
INSERT INTO test01(id, start_time, end_time) VALUES (1, '2021-08-18 16:27:51', '2021-08-18 17:27:59');
INSERT INTO test01(id, start_time, end_time) VALUES (2, '2021-08-18 17:20:26', '2021-08-18 20:10:37');
INSERT INTO test01(id, start_time, end_time) VALUES (3, '2021-08-18 22:05:57', '2021-08-18 23:55:20'); 

Create a stored procedure:

CREATE PROCEDURE sumTime()
BEGIN
    -- Define variable -- Is it the first time to DECLARE is_old int(1) DEFAULT 0;
 
    -- Last data DECLARE old_start_time datetime;
	DECLARE old_end_time datetime;
 
	-- This data DECLARE start_time datetime;
	DECLARE end_time datetime;
 
	-- Return result DECLARE num int(32) DEFAULT 0;
 
	-- Loop end switch DECLARE done int DEFAULT 0;
 
	-- Create a cursor (query database data)
	DECLARE list CURSOR FOR SELECT a.start_time, a.end_time FROM test01 a;
 
    -- Define the last loop and set the loop end switch to 1
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
 
	--Open cursor OPEN list;
 
		-- Open loop posLoop:LOOP
			-- Take the value of the current loop and assign it to the current data variable FETCH list INTO start_time,end_time;
			-- Determine if it is the first time if (is_old = 0) THEN 
 
				SET is_old = 1;
				SET old_start_time = start_time;
				SET old_end_time = end_time;
 
			-- ELSE
				-- Check if it is within the interval if (start_time >= old_start_time AND start_time <= old_end_time) THEN
 
					-- Check if the end time is not in the interval if (end_time < old_start_time OR end_time > old_end_time) THEN
						SET old_end_time = end_time;
				   END IF;
 
				 -- ELSE
 
				   if (start_time < old_start_time ) THEN
 
						SET old_start_time = start_time;
 
					 ELSE
 
						SET num = num + TIMESTAMPDIFF(MINUTE, old_start_time, old_end_time);
						SET old_start_time = start_time;
						SET old_end_time = end_time;
					 END IF;
				 END IF;
			END IF;
			-- Check if it is the last loop IF done=1 THEN 
			    SET num = num + TIMESTAMPDIFF(MINUTE, old_start_time, old_end_time);
			    LEAVE posLoop;
			END IF;
		-- End the loop	
		END LOOP posLoop;
	-- Close the cursor CLOSE list;
	SELECT num;
END;
-- Call the stored procedure call sumTime(); 

-- Delete the stored procedure drop procedure if exists sumTime;

This is the end of this article about how to remove overlapping time and calculate the time difference in MySQL. For more relevant content about calculating the time difference in MySQL, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in:
  • MySQL calculates the number of days, months, and years between two dates
  • mysql calculate time difference function
  • Detailed explanation of the reason why the MySQL 5.7 slow query log time is 8 hours behind the system time
  • How to get the time difference between two times in mysql query

<<:  Not a Chinese specialty: Web development under cultural differences

>>:  Detailed Example of CSS3 box-shadow Property

Recommend

Detailed explanation of the usage of 5 different values ​​of CSS position

The position property The position property speci...

Detailed explanation of vue-router 4 usage examples

Table of contents 1. Install and create an instan...

JavaScript to achieve floor effect

This article shares the specific code of JavaScri...

Detailed tutorial on installing pxc cluster with docker

Table of contents Preface Preliminary preparation...

File upload via HTML5 on mobile

Most of the time, plug-ins are used to upload fil...

How to create a child process in nodejs

Table of contents Introduction Child Process Crea...

Detailed explanation of MySQL information_schema database

1. Overview The information_schema database is th...

Summary of Vue 3 custom directive development

What is a directive? Both Angular and Vue have th...

Linux kernel device driver proc file system notes

/***************** * proc file system************...

WeChat applet implements form verification

WeChat applet form validation, for your reference...

Loading animation implemented with CSS3

Achieve results Implementation Code <h1>123...

How to install jupyter in docker on centos and open ports

Table of contents Install jupyter Docker port map...

How to use union all in MySQL to get the union sort

Sometimes in a project, due to some irreversible ...