数据库sql中判断时间冲突 - 服务器托管|北京服务器租用|机房托管租用|IDC托管租用|机房机柜带宽租用-价格及费用咨询

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

1. s

比如yyyy-MM-dd HH:mm:ss格式的数据, 多用于判断预约时间和每日排班冲突.
对于冲突的情况使用列举法有

s' : 新时间段在已有时间左边, 不包含, 情况1
s' : 新时间段和已有时间左边有交集, 情况2
s : 新时间段在已有时间内, 被包含关系, 也即在已有时间段内部, 情况3
s : 新时间段和已有时间右边有交集, 情况4
s' : 新时间和已有时间是是包含关系, 也即新时间段在已有时间段外部, 情况5
s : 新时间段在已有时间右边, 不包含, 情况6 除去开始和最后的不包含, 可以得到当s s'时候两个时间端肯定有交集, 也即冲突.

2. 存在s > e

比如HH:mm:ss格式的, 多用于固定早中晚班定义 / 营业时间等周期性活动的时间冲突判断.

2.1 分类逐步分析

2.1.1 s

s’ s s’即可

s服务器托管网‘ > e’: 也即跨天的时候, 分成`s' - 24` 和 `0 - e'`

前半段: 同1; (同下面24肯定大于s, 逻辑上可以与第一种情况写到一起)

下半段: 0

2.1.2 s > e

同理拆分成s – 24 和 0 – e.

s’
前半段: 24肯定大于s’, 只需要判断s
后半段: 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 s') 或者 (s1 s1'))

2.2.2 分情况论证

当两个都是跨天时候
- 因为e,s1,e’,s1’分别是24,0,24,0
- 所以式子可以写为: ((s s') 或者 (0 0))
- 进一步得出: 两边都恒真, 也即两个都跨天时候肯定有重合的.
- 0点肯定在的, 故成立. 见2.1.2下第二条
当只s,e跨天时候
- 因为e,s1,s1′,e1’分别是24,0,s’,e’
- 所以式子可以写为: ((s s') 或者 (0 s'))
- 进一步可以得出: 只需要判断(s s')即可
- 数据库跨天, 传入不跨, 见2.12下的第一条(e1始终是2.1中的e)
当只s’,e’跨天时候
- 因为s1,e1,e’,s1’分别是s,e,24,0
- 所以式子可以写为: ((s s') 或者 (s 0))
- 进一步可以得出: 只需要判断(s s')即可
- 数据库不跨, 传入跨, 见2.1.1的第二条(e1’始终是2.1中的e’)
当都不跨天时候
- 因为s1,e1,s1′,e1’分别是s,e,s’,e’
- 所以式子可以写为: ((s s') 或者 (s s'))
- 进一步可以得出: 只需要判断s 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  s') || (s1  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截取. 不跨天时候存两遍

服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net
机房租用，北京机房租用，IDC机房托管， http://www.fwqtg.net