SQL Server中如何按从5:00开始的24小时间隔分组数据？

原数据表结构如下：

需要实现的需求：查询每个用户在当日5:00至次日5:00这个自定义时间段内的首次和末次时间（而非常规的0:00-24:00周期），预期结果如下：

本人初步思路是先判断每条记录所属的自定义日期，代码如下：

select 
    user,
    convert(datetime, date + ' ' + time, 121) as time,
    case 
        when convert(datetime, date + ' ' + time, 121) > convert(datetime, date + ' ' + "05:00:00", 121)   
            then convert(datetime, date, 101) 
        when convert(datetime, date + ' ' + time, 121) <= convert(datetime, date + ' ' + "05:00:00", 121) 
            then convert(datetime, date, 101) - 1 
    end as Belongdate 
from 
    Table

再按user和Belongdate分组得到结果，请问是否有其他解决方案？

替代解决方案 #

方案1：通过时间偏移简化归属日期计算 #

可以直接对完整的datetime值减去5小时，再取日期部分作为归属日期，逻辑更直观简洁：

WITH cte AS (
    SELECT 
        [user],
        CONVERT(datetime, [date] + ' ' + [time], 121) AS full_datetime,
        -- 时间前移5小时后取日期，即为该记录所属的自定义周期日期
        CAST(DATEADD(HOUR, -5, CONVERT(datetime, [date] + ' ' + [time], 121)) AS DATE) AS belong_date
    FROM [Table]
)
SELECT 
    [user],
    belong_date AS [date],
    MIN(full_datetime) AS firsttime,
    MAX(full_datetime) AS lasttime
FROM cte
GROUP BY [user], belong_date
ORDER BY belong_date DESC;

核心逻辑：将所有时间提前5小时后，原需求的当日5:00至次日5:00周期，就对应偏移后的当日0:00至次日0:00常规日期周期，直接取偏移后的日期即可完成分组，避免了冗余的case判断。

方案2：直接在分组条件中计算归属日期 #

如果不需要CTE，也可以直接在分组语句中完成归属日期计算，一步到位：

SELECT 
    [user],
    CAST(DATEADD(HOUR, -5, CONVERT(datetime, [date] + ' ' + [time], 121)) AS DATE) AS [date],
    MIN(CONVERT(datetime, [date] + ' ' + [time], 121)) AS firsttime,
    MAX(CONVERT(datetime, [date] + ' ' + [time], 121)) AS lasttime
FROM [Table]
GROUP BY [user], CAST(DATEADD(HOUR, -5, CONVERT(datetime, [date] + ' ' + [time], 121)) AS DATE)
ORDER BY [date] DESC;

方案说明 #

这两种方案和初始思路本质逻辑一致，都是先确定每条记录的自定义周期归属，再聚合求首尾时间，但通过DATEADD偏移的方式简化了归属日期的计算，代码更简洁易读，也减少了重复的CONVERT操作。

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