MySQL去除重叠时间求时间差和的实现

Nice MySql

2022-05-15 0 181

需求：

在生产中常常出现计算两个时间差的业务，比如总宕机时间、总开通会员时间等等。。。但是这些时间往往不是连贯的，断断续续，甚至可能会出现重叠的情况。无法直接求出时间差。

例如：

MySQL去除重叠时间求时间差和的实现

开车：

一开始，我想的是用单条SQL实现，例如：↓

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

我发现，数据库数据千千万，不可能这样，也不可能用UNION这种东西去拼接，数据很多，就一定会有循环，所以，在不使用Java语言的情况下，我选择尝试用存储过程来解决以下这个问题。

思路：

首先，一次进入循环的数据不会进行计算，防止后边的数据和它有重叠，

从第二条数据开始，就要判断开始时间是否和上一个数据重叠，如果重叠，则校验结束时间是否也重叠，如果重叠我就啥也不干，不重叠，则把这个值赋给上一次的数据的结束时间。

如果开始时间不再范围内，那么需要判断开始时间是在上一次时间的之前还是之后

如果这个范围之前，把这个值赋给上一次的数据的开始时间。

在这个范围之后，计算并赋值

最后一次循环也要计算并赋值

实现：

首先创建表，模拟数据

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');

MySQL去除重叠时间求时间差和的实现

创建存储过程：

CREATE PROCEDURE sumTime()
BEGIN
    -- 定义变量 
 
    -- 是否首次
    DECLARE is_old int(1) DEFAULT 0;
 
    -- 上一次数据
	DECLARE old_start_time datetime;
	DECLARE old_end_time datetime;
 
	-- 本次数据
	DECLARE start_time datetime;
	DECLARE end_time datetime;
 
	-- 返回结果
	DECLARE num int(32) DEFAULT 0;
 
	-- 循环结束开关
	DECLARE done int DEFAULT 0;
 
	-- 创建游标（查询数据库数据）
	DECLARE list CURSOR FOR SELECT a.start_time, a.end_time FROM test01 a;
 
    -- 定义最后一次循环时设置 循环结束开关 为 1
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
 
	-- 开启游标
	OPEN list;
 
		-- 开启循环
		posLoop:LOOP
			-- 取值 将当前循环的值取出 赋值给当前数据变量
			FETCH list INTO start_time,end_time;
			-- 判断是否首次
			if (is_old = 0) THEN 
 
				SET is_old = 1;
				SET old_start_time = start_time;
				SET old_end_time = end_time;
 
			-- 否则
			ELSE
				-- 校验是否在区间内
				 if (start_time >= old_start_time AND start_time <= old_end_time) THEN
 
					-- 校验结束时间是否不在在区间内
				   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;
			-- 校验是否最后一次循环
			IF done=1 THEN 
			    SET num = num + TIMESTAMPDIFF(MINUTE, old_start_time, old_end_time);
			    LEAVE posLoop;
			END IF;
		-- 结束循环	
		END LOOP posLoop;
	-- 关闭游标 
	CLOSE list;
	SELECT num;
END;

-- 调用存储过程
call sumTime();

MySQL去除重叠时间求时间差和的实现

-- 删除存储过程
drop procedure if exists sumTime;

到此这篇关于MySQL去除重叠时间求时间差和的实现的文章就介绍到这了,更多相关MySQL 求时间差和内容请搜索NICE源码以前的文章或继续浏览下面的相关文章希望大家以后多多支持NICE源码！

目录

需求：

开车：

思路：

实现：

微信