MySQL去除重叠时间求时间差和的实现
时间：2022-12-03 12:10:47 | 栏目：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');
创建存储过程：
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();
-- 删除存储过程 drop procedure if exists sumTime;