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:
|
<<: Not a Chinese specialty: Web development under cultural differences
>>: Detailed Example of CSS3 box-shadow Property
This article uses examples to illustrate the usag...
Table of contents Preface analyze Data Total Repe...
Table of contents Preface 1. Rendering 2. Code 3....
1. Create users and authorize Creating users and ...
RGB color table color English name RGB 16 colors ...
1. Pull the image First, execute the following co...
<br />The information on web pages is mainly...
Table of contents Introduction How to connect to ...
JS calculates the total price of goods in the sho...
This article example shares the specific code of ...
When I was printing for a client recently, he aske...
I recently encountered a problem at work. There i...
This article shares the specific code of JavaScri...
Table of contents Preface: Ubuntu 18.04 changes a...
I encountered a small problem today and struggled ...