TSQL:日期时间重叠和间隔值

试试这个:

WITH Times AS (
        SELECT DISTINCT(Start) AS Time FROM intervals
        UNION ALL
        SELECT DISTINCT([End]) AS Time FROM intervals),
     Days AS (SELECT DISTINCT DATEADD(dd, 0, DATEDIFF(dd, 0, Time)) AS Time FROM Times),
     Times2 AS (
        SELECT Time FROM times
        UNION ALL
        SELECT Time FROM days),
     Times3 AS (SELECT ROW_NUMBER() OVER (ORDER BY Time) AS rn, Time FROM Times2),
     Times4 AS (
        SELECT T1.Time AS Start, T2.Time AS [End]
        FROM Times3 T1
        JOIN Times3 T2
        ON T1.rn + 1 = T2.rn),
     IntervalParts AS (
         SELECT DISTINCT Times4.*
         FROM Times4
         JOIN intervals
         ON Times4.Start >= intervals.Start AND Times4.[End] <= intervals.[End]),
     IntervalsByDay AS (
         SELECT 
             DATEADD(dd, 0, DATEDIFF(dd, 0, Start)) AS Day,
             DATEDIFF(hh, Start, [End]) AS Duration 
         FROM IntervalParts)
SELECT Day, SUM(Duration) AS Duration
FROM IntervalsByDay
GROUP BY Day

结果:

Day                     Duration 
2009-01-01 00:00:00.000 18
2009-01-02 00:00:00.000 7
2009-01-03 00:00:00.000 8
2009-01-04 00:00:00.000 2
2009-01-05 00:00:00.000 10

要将其限制在某个范围内,只需添加一个适当的WHERE子句。

退房 DATEDIFF 在sqlserver中。我不确定您使用的是什么db服务器,但我想其他RDBMS也会有类似的功能,或者足以让您创建自己的功能来实现这一点。

SELECT start, DATEDIFF(hh, start, end) FROM ......