数据库sql中判断时间冲突
数据库现有数据其中两列: s - 开始时间, e - 结束时间. 在新插入数据s', e'之前需要判断两个时间之间是否有重合
因为使用mybatis-plus的缘故, 结论都使用s或e在符号前面.
- s < e
比如yyyy-MM-dd HH:mm:ss格式的数据, 多用于判断预约时间和每日排班冲突.
对于冲突的情况使用列举法有
s' < e' < s < e: 新时间段在已有时间左边, 不包含, 情况1
s' < s < e' < e: 新时间段和已有时间左边有交集, 情况2
s < s' < e' < e: 新时间段在已有时间内, 被包含关系, 也即在已有时间段内部, 情况3
s < s' < e < e': 新时间段和已有时间右边有交集, 情况4
s' < s < e < e': 新时间和已有时间是是包含关系, 也即新时间段在已有时间段外部, 情况5
s < e < s' < e': 新时间段在已有时间右边, 不包含, 情况6
除去开始和最后的不包含, 可以得到当s < e' 并且 e > s'时候两个时间端肯定有交集, 也即冲突.
image
- 存在s > e
比如HH:mm:ss格式的, 多用于固定早中晚班定义 / 营业时间等周期性活动的时间冲突判断.
2.1 分类逐步分析
2.1.1 s < e
s' < e': 同1, 判断s < e' 并且 e > s'即可
s' > e': 也即跨天的时候, 分成s' - 24 和 0 - e'
前半段: 同1; (同下面24肯定大于s, 逻辑上可以与第一种情况写到一起)
下半段: 0 < e, 只需要判断s < e'即可
2.1.2 s > e
同理拆分成s - 24 和 0 - e.
s' < e': 不跨天
前半段: 24肯定大于s', 只需要判断s < e'
后半段: 0肯定小于e', 只需要判断e > s'
s' > e': 跨天, 拆分成s'-24 和 0-e' (这种必有24, 答案恒真)
2.1.3 代码
对于s > e情况下, 虽然sql可以使用start_time > end_time, 但是在mybatis-plus中写不出来
所以在保存时候添加一列is_greater代表是否当条数据跨天:
// 保存跨天时候的结束时间, 将结束时间变成24:00:00
// 传过来的结束时间赋值给nextDayEndTime, 也即finalNextDayEndTime
String finalNextDayEndTime = nextDayEndTime;
List existList = configService.lambdaQuery()
.and(p -> p
// 按照开始小于结束判断(数据也是开始小于结束)
.and(q -> q.eq(BaseTimeConfig::getIsGreater, 0)
.and(q1 -> q1
.lt(BaseTimeConfig::getStartTime, endTime)
.gt(BaseTimeConfig::getEndTime, startTime))
// 开始大于结束时间时候(), 计算拆分出来的第二天也即0点 - endTime(复制给了nextDayEndTime)
// 需要((开始小于结束 和 开始大于结束拆分出第一天) or (开始大于结束拆分出第二天))满足其一即可
// 拆分出来的第二天是0开始, 结束时间肯定肯定比0大, 所以判断开始时间比nextDayEndTime小即可
.or(!"".equals(finalNextDayEndTime), q1 -> q1.lt(BaseTimeConfig::getStartTime, finalNextDayEndTime)))
// 按照开始小于结束判断(数据是开始大于结束的, 判断结束时间大于传入的开始时间即可,
// 因为数据库开始时间应该算0, 0肯定比传入的开始时间小于)
.or(q -> q.eq(BaseTimeConfig::getIsGreater, 1)
.and(q1 -> q1
// 后半截
.and(q2 -> q2.gt(BaseTimeConfig::getEndTime, startTime))
// 前半截
.or(q2 -> q2.lt(BaseLightTimeConfig1::getStartTime, baseLightTimeConfig.getEndTime())))
// 传入跨天的话, 数据库中跨天的都冲突
.or(!"".equals(finalNextDayEndTime), q2 -> q2.ne(BaseLightTimeConfig1::getId, 0))))
.ne(baseTimeConfig.getId() != null && baseTimeConfig.getId() > 0,
BaseTimeConfig::getId, baseTimeConfig.getId())
.isNull(BaseTimeConfig::getDeletedAt)
.list();
2.1.4 总结
总体思路清晰, 逐步分析得到, 但不巧妙
2.2 预处理
2.2.1 提出假设
在保存s和e时候, 如果是跨天的拆分成s,24,0,e; 不跨天的话使用s,e,s,e, 对应字段s,e,s1,e1
对于s'和e'来说也是同上, 拆分出s',e',s1',e1'
分别比较s,e和s',e' 与 s1,e1和s1',e1', 两个条件使用或者连接, 也即: ((s < e' 并且 e > s') 或者 (s1 < e1' 并且 e1 > s1'))
2.2.2 分情况论证
当两个都是跨天时候
因为e,s1,e',s1'分别是24,0,24,0
所以式子可以写为: ((s < 24 并且 24 > s') 或者 (0 < e1' 并且 e1 > 0))
进一步得出: 两边都恒真, 也即两个都跨天时候肯定有重合的.
0点肯定在的, 故成立. 见2.1.2下第二条
当只s,e跨天时候
因为e,s1,s1',e1'分别是24,0,s',e'
所以式子可以写为: ((s < e' 并且 24 > s') 或者 (0 < e' 并且 e1 > s'))
进一步可以得出: 只需要判断(s < e') 或者 (e1 > s')即可
数据库跨天, 传入不跨, 见2.12下的第一条(e1始终是2.1中的e)
当只s',e'跨天时候
因为s1,e1,e',s1'分别是s,e,24,0
所以式子可以写为: ((s < 24 并且 e > s') 或者 (s < e1' 并且 e > 0))
进一步可以得出: 只需要判断(s < e1') 或者 (e > s')即可
数据库不跨, 传入跨, 见2.1.1的第二条(e1'始终是2.1中的e')
当都不跨天时候
因为s1,e1,s1',e1'分别是s,e,s',e'
所以式子可以写为: ((s < e' 并且 e > s') 或者 (s < e' 并且 e > s'))
进一步可以得出: 只需要判断s < e' 并且 e > s'即可
都不跨天, 也即都增, 见1 或 2.1.1的第一条
综上, 在保存se时候, 如果保存成四个字段, 如果跨天: s,24,0,e; 如果不跨天就s,e,s,e; 同理s',e'也相同规则拆分开
使用前面两个和前面两个比较, 后面两个与后面两个比较的结论成立.
(一个跨天, 一个不跨天时候, 使用不跨天的必须要与跨天的两端比较, 那就不跨天时候存两遍, 这样就都比较了)
2.2.3 代码
// s', e'
String startTime = "22:00:00", endTime = "24:00:00";
// s1', e1'
String startTime1 = "00:00:00", endTime1 = "03:00:00";
// ((s < e' 并且 e > s') || (s1 < e1' 并且 e1 > s1'))
configService.lambdaQuery()
.and(q -> q
.lt(BaseTimeConfig1::getS, endTime)
.gt(BaseTimeConfig1::getE, startTime))
.or(q -> q
.lt(BaseTimeConfig1::getS1, endTime1)
.gt(BaseTimeConfig1::getE1, startTime1))
.list();
2.2.4 总结
分两段存, 跨天时候24截取. 不跨天时候存两遍
好文要顶 关注我 收藏该文
Codorld
粉丝 - 8 关注 - 4
+加关注
1
« 上一篇： springboot整合nacos和dubbo
posted @ 2023-09-21 10:06 Codorld 阅读(94) 评论(0) 编辑 收藏 举报