时间段搜索 sql

背景

数据库中有一张视频分片表，长这样

CREATE TABLE `video` (
  `id` bigint(20) NOT NULL COMMENT '主键',
  `start_time` datetime DEFAULT NULL COMMENT '视频开始时间',
  `end_time` datetime DEFAULT NULL COMMENT '视频结束时间',
  `video_url` varchar(200) DEFAULT NULL COMMENT '视频地址',
  `video_size` varchar(200) DEFAULT NULL COMMENT '视频大小',
  `create_time` datetime(3) DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
  `update_time` datetime(3) DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='视频信息表';

每一条记录都是一个分片，包含了一段时间的视频

要求

现在要求，前端选择一段时间后，后端查询这一段时间内的视频

思路

如上图，随着“用户选择的开始时间”不断的右移，"视频"这条数据，从符合条件，最后变成不符合条件。那么它是从什么时候开始不符合条件的呢？

从上图可以看到，是从数据的结束时间小于“用户选择的开始时间” 开始的！也就是說，要想符合条件，数据的结束时间大于等于“用户选择的开始时间”

同理，随着“用户选择的结束时间”不断的左移，原来符合条件的数据，最后变成不符合条件。什么时候开始不符合条件的呢？是从数据的开始时间大于“用户选择的结束时间”开始的！也就是說，要想符合条件，数据的开始时间小于“用户选择的结束时间””

两条规律相加，就得到我们的筛选标准

数据的开始时间小于“用户选择的结束时间”
数据的结束时间大于“用户选择的开始时间”

sql

SELECT
    * 
FROM
    video 
WHERE
    start_time <= '用户选择的结束时间' 
    AND end_time >= '用户选择的开始时间'

后记

还有一种特殊情况上面没考虑到，如下

但上面的算法依然满足这种特殊情况