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;
到此这篇关于MySQL去除重叠时间求时间差和的实现的文章就介绍到这了,更多相关MySQL 求时间差和内容请搜索猪先飞以前的文章或继续浏览下面的相关文章希望大家以后多多支持猪先飞!
相关文章
- 这篇文章主要介绍了MySQL性能监控软件Nagios的安装及配置教程,这里以CentOS操作系统为环境进行演示,需要的朋友可以参考下...2015-12-14
- 这篇文章主要介绍了在java中获取List集合中最大的日期时间操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2020-08-15
- 这篇文章主要介绍了教你怎么用Java获取国家法定节假日,文中有非常详细的代码示例,对正在学习java的小伙伴们有非常好的帮助,需要的朋友可以参考下...2021-04-23
- 新版 Mysql 中加入了对 JSON Document 的支持,可以创建 JSON 类型的字段,并有一套函数支持对JSON的查询、修改等操作,下面就实际体验一下...2016-08-23
mysql中获取一天、一周、一月时间数据的各种sql语句写法
创建表:复制代码 代码如下:create table if not exists t( id int, addTime datetime default '0000-00-00 00:00:00′)添加两条初始数据:insert t values(1, '2012-07-12 21:00:00′);insert t values(2, '2012-07...2014-05-31- 这篇文章主要介绍了.NET/C# 使用Stopwatch测量运行时间,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧...2020-06-25
深入研究mysql中的varchar和limit(容易被忽略的知识)
为什么标题要起这个名字呢?commen sence指的是那些大家都应该知道的事情,但往往大家又会会略这些东西,或者对这些东西一知半解,今天我总结下自己在mysql中遇到的一些commen sense类型的问题。 ...2015-03-15- 这篇文章主要介绍了MySQL 字符串拆分操作(含分隔符的字符串截取),具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-22
- 一、先说一下为什么要分表:当一张的数据达到几百万时,你查询一次所花的时间会变多,如果有联合查询的话,有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。根据个人经验,mysql执行一个sql的过程如下:1...2014-05-31
- 我们自己鼓捣mysql时,总免不了会遇到这个问题:插入中文字符出现乱码,虽然这是运维先给配好的环境,但是在自己机子上玩的时候咧,总得知道个一二吧,不然以后如何优雅的吹牛B。...2015-03-15
- 宿主机使用网线的时候,客户机在Bridged Adapter模式下,使用Atheros AR8131 PCI-E Gigabit Ethernet Controller上网没问题。 宿主机使用无线的时候,客户机在Bridged Adapter模式下,使用可选项里唯一一个WIFI选项,Microsoft Virtual Wifi Miniport Adapter也无法上网,故弃之。...2013-09-19
- 这几天在centos下装mysql,这里记录一下安装的过程,方便以后查阅Mysql5.5.37安装需要cmake,5.6版本开始都需要cmake来编译,5.5以后的版本应该也要装这个。安装cmake复制代码 代码如下: [root@local ~]# wget http://www.cm...2015-03-15
- 常用的日期时间正则表达式 下面收藏了大量的日期时间正则匹配函数,包括分钟,时间与秒都能达到。 正则表达式 (?n:^(?=d)((?<day>31(?!(.0?[2469]|11))|30(?!.0?2)|29(...2016-11-25
- 实例讲解之前,先来介绍几个核心函数: mktime 函数 mktime() 函数返回一个日期的 Unix 时间戳。 参数总是表示 GMT 日期,因此 is_dst 对结果没有影响。 参数可以从右到左依次空着,空着的参数会被设为相应的当前 GMT 值。...2015-11-08
- 想在网页中动态地显示当前系统的时间,找了好多,不过都是一些停在那里不动的。。。不过皇天不负有心人,终于让我找到了...2020-06-25
- 首先要声明一点,大部分情况下,修改MySQL密码是需要有mysql里的root权限的...2013-09-11
- 这篇文章主要介绍了postgresql 中的时间处理小技巧(推荐),本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下...2021-03-29
- MySQL命令行导出数据库: 1,进入MySQL目录下的bin文件夹:cd MySQL中到bin文件夹的目录 如我输入的命令行:cd C:/Program Files/MySQL/MySQL Server 4.1/bin (或者直接将windows的环境变量path中添加该目录) ...2013-09-26
- 我打开android开发手册的时候:http://www.csdn123.com/html/android/reference/packages.html 发现打开速度很慢,我用按了一下F12打开调试面板,切换到网络的选项卡network...2016-05-19
- 一、连接Mysql格式: mysql -h主机地址 -u用户名 -p用户密码1、连接到本机上的MYSQL。首先打开DOS窗口,然后进入目录mysql/bin,再键入命令mysql -u root -p,回车后提示你输密码.注意用户名前可以有空格也可以没有空格,但是密...2015-11-08