sql datetime whether two date range overlap

碰到一个需求，一个时间段内只允许有一个计划，也就是说数据库中的时间区间不能有重复或交叉，记录下从复杂到简单的简化过程。

1.刚开始想的时候感觉这个有很多种情况，然后把每一次情况都用sql标识出来，写的语句如下。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
#情况如下：
#  a:       |------|
#  b:   |------|
#  a.start_time >= start_at and a.start_time <= end_at
#  a: |------| 
#  b:    |------|
#  a.end_time >= start_at and a.end_time <= end_at
#  a:   |------|
#  b: |----------|
#  a.start_time >= start_at and a.end_time <= end_at
#  a: |----------|
#  b:   |------|
#  a.start_time <= start_at and a.end_time >= end_at
#代码如下：
plan = Plan.where('((start_time >= ? and start_time <= ?) or (end_time >= ? and end_time <= ?) or (start_time  >=  ? and end_time <= ?) or (start_time <= ? and end_time >= ?) )',start_at, end_at, start_at, end_at, start_at, end_at, start_at, end_at)

2.后来感觉太复杂了，就在论坛里发帖问了下，rails代码可以这么写

1
plan = Plan.where('((start_time >= :start_at and start_time <= :end_at) or (end_time >= :start_at and end_time <=  :end_at) or (start_time  >= :start_at and end_time <=  :end_at) or (start_time <= :start_at and end_time >=  :end_at) )',start_at: start_at, end_at: end_at)

3.后来优化了一次，发现好多情况其实都是重复的，优化之后的情况为：

1
2
3
4
5
6
7
8
#  a:       |------|
#  b:   |------|
#  a.start_time >= start_at and a.start_time <= end_at
#  a: |------| 
#  b:    |------|
#  a.start_time <= start_at and a.end_time >= start_at
plan = Plan.where('((start_time >= :start_at and start_time <= :end_at) or (start_time <= :start_at and end_time >=  :start_at) )',start_at: start_at, end_at: end_at)

4.@quakewang 帮着理了一下思路，最后的结果为：

先是判断时间区段不重叠，有2种情况

1
2
3
4
5
6
7
                        |start_a ----- end_a| 
|start_b ----- end_b|                       
---------------------------------------------
|start_a ----- end_a|                        
                        |start_b ----- end_b|

得出不重叠的表达式是：

1
start_a > end_b || end_a < start_b

那么不重叠就是取反

1
!(start_a > end_b || end_a < start_b)

布尔运算等价于

1
start_a <= end_b and end_a >= start_b

最终的结果为：

1
plan = Plan.where('start_time <= ? and end_time >= ?', end_at, start_at）

参考链接1
参考链接2