SQL Server中基于时间周期匹配Datetime到时间窗口标签的问题

需要为任意给定的datetime值匹配仅由时间部分决定的标签，但由于时间的循环特性（如tag3的时间区间跨午夜），无法通过简单的大小比较实现。尝试过类型转换、时间偏移等方法，但逻辑复杂且未解决问题。

示例数据 #

DECLARE @tagtable TABLE (tag varchar(10),[start] time,[end] time);
DECLARE @datetimestable TABLE ([timestamp] datetime)

Insert Into @tagtable (tag, [start], [end])
values ('tag1','04:00:00.0000000','11:59:59.9999999'),
('tag2','12:00:00.0000000','19:59:59.9999999'),
('tag3','20:00:00.0000000','03:59:59.9999999');

Insert Into @datetimestable ([timestamp])
values ('2022-07-24T23:05:23.120'),
('2022-07-27T13:24:40.650'),
('2022-07-26T09:00:00.000');

tagtable 表结构及数据 #

期望结果 #

示例输入的datetime值：2022-07-24 23:05:23.120、2022-07-27 13:24:40.650、2022-07-26 09:00:00.000

期望输出：

曾尝试的代码 #

SELECT 
IIF(Datepart(Hour, a.[timestamp]) > 19, 
   Cast(Dateadd(Day,1,a.[timestamp]) as Date), 
   Cast(a.[timestamp] as Date)
  ) as [date], 
b.[tag]
FROM @datetimestable a
INNER JOIN @tagtable b 
   ON SomethingWith(a.[timestamp]) 
      between SomethingWith(b.[start]) and SomethingWith(b.[end])

解决方法 #

核心思路是区分不跨午夜（start <= end）和跨午夜（start > end）两种时间区间类型，分别设置匹配条件，同时正确计算对应的日期：

SELECT
    -- 计算对应的日期：tag3且时间在20:00-23:59时，日期为次日；其余情况为原日期
    CASE
        WHEN b.tag = 'tag3' AND CAST(a.[timestamp] AS time) >= '20:00:00.0000000'
            THEN DATEADD(DAY, 1, CAST(a.[timestamp] AS date))
        ELSE CAST(a.[timestamp] AS date)
    END AS [date],
    b.tag
FROM @datetimestable a
INNER JOIN @tagtable b ON
    -- 匹配不跨午夜的区间（tag1、tag2）
    (b.[start] <= b.[end] AND CAST(a.[timestamp] AS time) BETWEEN b.[start] AND b.[end])
    -- 匹配跨午夜的区间（tag3）：时间>=20:00 或者 时间<=03:59
    OR (b.[start] > b.[end] AND (CAST(a.[timestamp] AS time) >= b.[start] OR CAST(a.[timestamp] AS time) <= b.[end]))

逻辑说明 #

1. 区间匹配：
   • 对于start <= end的标签（tag1、tag2），直接用时间部分BETWEEN起始和结束时间；
   • 对于start > end的标签（tag3），时间部分要么大于等于起始时间（20:00之后），要么小于等于结束时间（03:59之前）。
2. 日期计算：
   • 当匹配tag3且时间在20:00-23:59时，对应的日期是原datetime的次日；
   • 其他所有情况，日期都是原datetime的日期部分。

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