数据库sql中判断时间冲突

数据库现有数据其中两列: 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(BaseTimeConfig::getStartTime, endTime)))))
	.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截取. 不跨天时候存两遍