数据库SQL如何判断时间冲突

蹲仔8个月前更新

100

数据库现有数据其中两列: s – 开始时间, e – 结束时间. 在新插入数据s’, e’之前需要判断两个时间之间是否有重合
因为使用mybatis-plus的缘故, 结论都使用s或e在符号前面.

1. 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’时候两个时间端肯定有交集, 也即冲突.

2. 存在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<BaseTimeConfig> 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();