如何为递归CTE生成的日期减去指定时间偏移量以统计时段停机时长？

没问题，我帮你修改了查询，现在可以精准按「前一天7:15至次日7:15」的时段作为一个统计日来计算停机时长，而且哪怕某天没有停机记录，也会自动输出0值的记录：

WITH dates AS (
    -- 这里替换成你要统计的起始日期，比如 '2024-05-01'
    SELECT CONVERT(date, '2024-05-01') AS dte
    UNION ALL
    -- 自动生成后续日期，直到起始日期的一个月后
    SELECT DATEADD(day, 1, dte) 
    FROM dates 
    WHERE dte < DATEADD(month, 1, '2024-05-01')
)
SELECT 
    CONVERT(datetime, d.dte) AS 统计日期,
    -- 空值直接转0，确保每个日期都有统计结果
    ISNULL(SUM(b.long_stop_minutes), 0) AS 当日停机总时长(分钟)
FROM dates d
LEFT JOIN long_stops_table b 
    -- 核心：把停机记录的时间戳偏移后映射到对应的统计日期
    ON CAST(DATEADD(minute, -(7 * 60 + 15), b.t_stamp) AS DATE) + 1 = d.dte
    AND b.Type = 'downtime'
GROUP BY CONVERT(datetime, d.dte)
ORDER BY CONVERT(datetime, d.dte) ASC
-- 设置递归深度为31，足够覆盖一个月的所有天数
OPTION (MAXRECURSION 31);

关键调整点说明 #

• 日期偏移逻辑：
  我用 DATEADD(minute, -(7*60+15), b.t_stamp) 把每条停机记录的时间戳往后挪7小时15分钟，取日期后再加1天，这样就能把「前一天7:15到当天7:15」的所有记录都归到当天的统计日期下。举个例子：2024-04-30 07:15的记录会被映射到2024-05-01的统计日，完全符合你的需求。

• 递归CTE优化：
  把原查询里硬写的结束日期改成了 DATEADD(month, 1, 起始日期)，这样你只需要改起始日期，自动就能覆盖整一个月的统计周期。另外加了 OPTION (MAXRECURSION 31)，避免递归深度超出默认限制（虽然默认是100，但设成31更贴合月度场景）。

• 空值处理：
  保留了 ISNULL(SUM(...), 0)，这样哪怕某天没有任何停机记录，也会返回0而不是NULL，统计结果更完整。

可选：验证时段范围 #

如果你想确认每个统计日期对应的实际时间区间，可以在查询里加上这两个字段：

SELECT 
    CONVERT(datetime, d.dte) AS 统计日期,
    DATEADD(minute, 7*60+15, DATEADD(day, -1, d.dte)) AS 时段开始时间,
    DATEADD(minute, 7*60+15, d.dte) AS 时段结束时间,
    ISNULL(SUM(b.long_stop_minutes), 0) AS 当日停机总时长(分钟)
FROM dates d
LEFT JOIN long_stops_table b 
    ON CAST(DATEADD(minute, -(7 * 60 + 15), b.t_stamp) AS DATE) + 1 = d.dte
    AND b.Type = 'downtime'
GROUP BY CONVERT(datetime, d.dte)
ORDER BY CONVERT(datetime, d.dte) ASC
OPTION (MAXRECURSION 31);

这样就能一目了然地看到每个统计日期对应的具体时段是否正确啦。

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