如何编写SQL WHERE条件筛选2021年5-8月周日19点至周五5点的记录

实现逻辑 #

• 第一层先限定整体时间范围，和原有查询的时间边界对齐，缩小数据扫描范围提升性能
• 第二层按周内日期拆分判断规则，仅保留符合时段要求的记录

不同数据库方言写法 #

SQL Server 版本

注：默认@@DATEFIRST设置为7（周日为一周第一天，周日返回值为1），如果你的库有自定义周起始规则，调整判断对应的数字即可

WHERE 
l1.[PickupTime] BETWEEN '2021/05/01 19:00:00' AND '2021/09/01 05:00:00'
AND (
    -- 周日19:00之后符合
    (DATEPART(WEEKDAY, l1.[PickupTime]) = 1 AND CAST(l1.[PickupTime] AS TIME) >= '19:00:00')
    -- 周一到周四全天符合
    OR DATEPART(WEEKDAY, l1.[PickupTime]) BETWEEN 2 AND 5
    -- 周五05:00之前符合
    OR (DATEPART(WEEKDAY, l1.[PickupTime]) = 6 AND CAST(l1.[PickupTime] AS TIME) <= '05:00:00')
)

MySQL 版本

注：DAYOFWEEK默认规则为周日返回1，周六返回7

WHERE 
l1.`PickupTime` BETWEEN '2021-05-01 19:00:00' AND '2021-09-01 05:00:00'
AND (
    (DAYOFWEEK(l1.`PickupTime`) = 1 AND TIME(l1.`PickupTime`) >= '19:00:00')
    OR DAYOFWEEK(l1.`PickupTime`) BETWEEN 2 AND 5
    OR (DAYOFWEEK(l1.`PickupTime`) = 6 AND TIME(l1.`PickupTime`) <= '05:00:00')
)

内容的提问来源于stack exchange，提问作者Amit Joshi