mysql 查询时间交集

Mysql通用模板
SELECT * from activity where
# 全部包含(子集)和左边包含
("开始时间" <=activity_end_time and activity_end_time <="结束时间")
# 右边包含
or ("开始时间"<=activity_start_time and activity_start_time <="结束时间")
# 全部包含(被子集)
or (activity_start_time <= "开始时间" and "结束时间" <= activity_end_time)

例如:

我们需要查询的时间段是 [ "2022-04-28 00:00:00" , "2022-05-04 00:00:00" ] 涉及的数据

SELECT * from activity where
# 全部包含(子集)和左边包含
("2022-04-28 00:00:00" <=activity_end_time and activity_end_time <="2022-05-04 00:00:00")
# 右边包含
or ("2022-04-28 00:00:00"<=activity_start_time and activity_start_time <="2022-05-04 00:00:00")
# 全部包含(被子集)
or (activity_start_time <= "2022-04-28 00:00:00" and "2022-05-04 00:00:00" <= activity_end_time)

 

原文链接:https://blog.csdn.net/jsonms/article/details/124925877