sql-server - 将日期时间评估为时间窗口

Question

我试图为任何给定的日期时间建立一个完全依赖于时间部分的标签。然而，因为时间部分是循环的，所以我不能让它在简单的大于条件下工作。我尝试了很多转换并将一次转换为 24 小时标记以打破循环但是它变得越来越复杂并且仍然不起作用。

我正在使用 SQL-Server，情况如下:

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');

可标记:

<表类="s-表"><头>标签开始结束<正文>标签104:00:00.000000011:59:59.9999999标签212:00:00.000000019:59:59.9999999标签320:00:00.000000003:59:59.9999999

对于给定的日期时间，例如2022-07-24 23:05:23.120, 2022-07-27 13:24:40.650, 2022-07-26 09:00:00.000

期望的结果是:

<表类="s-表"><头>日期标签<正文>2022-07-25标签32022-07-27标签22022-07-26标签1

正如我所写的，我试图通过强制转换、添加和日期差异来扭曲它

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

Answer 1

这里唯一棘手的一点是你的标签时间范围可以超过午夜，所以你需要检查你的时间是在开始和结束之间，还是在 start 和之间跨越午夜23:59:59 或 00:00:00 和 end 之间。

唯一的另一部分是使用 CTE 将您的 timestamp 列拆分为日期和时间，以避免重复转换。

;WITH splitTimes AS
(
    SELECT CAST(timestamp AS DATE) as D,
     CAST(timestamp AS TIME) AS T
    FROM @datetimestable
)
SELECT 
DATEADD(
   day,
   CASE WHEN b.[end]<b.start THEN 1 ELSE 0 END,
   a.D) as timestamp,
b.[tag]
FROM [splitTimes] a
INNER JOIN @tagtable b 
   ON a.T between b.[start] and b.[end]
   OR (b.[end]<b.start AND (a.T BETWEEN b.[start] AND '23:59:59.99999'
   OR a.T BETWEEN '00:00:00' AND b.[end]))

实例:https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=506aef05b5a761afaf1f67a6d729446c