本网站(662p.com)打包出售,且带程序代码数据,662p.com域名,程序内核采用TP框架开发,需要联系扣扣:2360248666 /wx:lianweikj
精品域名一口价出售:1y1m.com(350元) ,6b7b.com(400元) , 5k5j.com(380元) , yayj.com(1800元), jiongzhun.com(1000元) , niuzen.com(2800元) , zennei.com(5000元)
需要联系扣扣:2360248666 /wx:lianweikj
MySQL去除重叠时间求时间差和的实现
我是陈晓 · 229浏览 · 发布于2021-08-24 +关注

在生产中常常出现计算两个时间差的业务,比如总宕机时间、总开通会员时间等,本文就详细的来介绍一下如何计算,感兴趣的可以了解一下

  

         我个人并不推荐在实际开发中使用存储过程,充满了各种的不方便,之所以写这东西,全在于学习,如果有高手看到我的内容有问题,可以随时指出或向我开炮。 

需求:

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

例如:

 开车:

        一开始,我想的是用单条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;



相关推荐

使用SELECT语句检索数据

奔跑的男人 · 793浏览 · 2019-06-03 09:33:43
部署MySQL延迟从库的几个好处

吴振华 · 657浏览 · 2019-05-14 21:57:51
MongoDB凭什么跻身数据库排行前五?

iamitnan · 716浏览 · 2019-06-18 10:04:56
Oracle开启和关闭的几种模式

qq2360248666 · 738浏览 · 2019-06-04 10:18:47
加载中

0评论

评论
我是一名在上海一家互联网公司上班,专注技术开发工作等。
小鸟云服务器
扫码进入手机网页