MySQL去除重叠时间求时间差和的实现
16lz
2021-12-09
需求:
在生产中常常出现计算两个时间差的业务,比如总宕机时间、总开通会员时间等等。。。但是这些时间往往不是连贯的,断断续续,甚至可能会出现重叠的情况。无法直接求出时间差。
例如:
开车:
一开始,我想的是用单条SQL实现,例如:↓
SELECT TIMESTAMPDIFF(MINUTE, '2021-08-19 14:30:00', '2021-08-19 15:00:00') FROM DUAL;
思路:
首先,一次进入循环的数据不会进行计算,防止后边的数据和它有重叠,
从第二条数据开始,就要判断开始时间是否和上一个数据重叠,如果重叠,则校验结束时间是否也重叠,如果重叠我就啥也不干,不重叠,则把这个值赋给上一次的数据的结束时间。
如果开始时间不再范围内,那么需要判断开始时间是在上一次时间的之前还是之后
如果这个范围之前,把这个值赋给上一次的数据的开始时间。
在这个范围之后,计算并赋值
最后一次循环也要计算并赋值
实现:
首先创建表,模拟数据
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; -- 定义最后一次循环时设置 循环结束开关 为 1DECLARE 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) THENSET 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;
更多相关文章
- 修改android系统和watchdog的延时
- Android(安卓)Calendar使用过程中遇到的问题
- Android执行shell命令
- Android中的AlarmManager的使用
- android获得系统时间(Handler)
- Android简易闹钟实现
- Android(安卓)时间滚轴
- [置顶] android 捕捉异常
- Android:HttpURLConnection使用,Tomcat的Servlet