选择在一天中特定时间内发生的部分持续时间。

Question

在Server中，我有一个具有启动时间和结束时间的进程表，我可以从该表中使用DATEDIFF计算持续时间。

Name        StartTime           EndTime
------------------------------------------------
process1    2016-10-10 11:10    2016-10-10 11:20
process2    2016-10-10 11:40    2016-10-10 12:30

如何选择在一天中特定时间(11和12)秒内发生的进程持续时间？

所以process1在11小时是10分钟，process2在11小时是20分钟，12小时是30分钟。

Answer 1

下面是一个示例，它将为每个源记录生成小时记录。它使用递归的CTE从每个记录的StartTime通过EndTime移动。在您的情况下，它可能需要进行一些轻微的修改，但希望您能够了解这个方法是如何工作的。

注意，如下面的示例所示，即使时间跨度跨越日期边界，这也会正常工作。

--==================================================================================
-- Do some quick setup to get a temporary table populated with data to use:
--==================================================================================
IF OBJECT_ID('tempdb..#ProcessHistory', 'U') IS NOT NULL DROP TABLE #ProcessHistory;
CREATE TABLE #ProcessHistory (
    Name VARCHAR(20),
    StartTime DATETIME,
    EndTime DATETIME
)

INSERT INTO #ProcessHistory
VALUES  ('process1', '2016-10-10 11:10', '2016-10-10 11:20'),
        ('process2', '2016-10-10 11:40', '2016-10-10 12:30'),
        ('process3', '2016-10-10 22:21', '2016-10-11 02:36');

--==================================================================================
-- Use a recursive CTE to generate hourly data for each record:
--==================================================================================
WITH HourlyData AS (
    -- Anchor:
    SELECT
        ph.Name [ProcessName],
        ph.StartTime [StartTime],
        ph.EndTime [EndTime],
        -- Get the current hour with date:
        DATEADD(MINUTE, -DATEPART(MINUTE, ph.StartTime), ph.StartTime) [CurrentHour],
        -- Calculate the next hour for use later:
        DATEADD(MINUTE, 60 - DATEPART(MINUTE, ph.StartTime), ph.StartTime) [NextHour],
        -- Determine how many minutes the process was active this hour:
        CASE
            WHEN DATEDIFF(MINUTE, ph.StartTime, ph.EndTime) > 60 - DATEPART(MINUTE, ph.StartTime)
                THEN 60 - DATEPART(MINUTE, ph.StartTime)
            ELSE DATEDIFF(MINUTE, ph.StartTime, ph.EndTime)
        END [Minutes]
    FROM #ProcessHistory ph

    UNION ALL

    -- Recurse:
    SELECT
        hd.ProcessName,
        hd.StartTime,
        hd.EndTime,
        hd.NextHour [CurrentHour],
        DATEADD(HOUR, 1, hd.NextHour) [NextHour],
        -- Determine how many minutes the process was active this hour:
        CASE
            WHEN DATEDIFF(MINUTE, hd.NextHour, hd.EndTime) < 60
                THEN DATEDIFF(MINUTE, hd.NextHour, hd.EndTime)
            ELSE 60
        END
    FROM HourlyData hd
    WHERE hd.NextHour < hd.EndTime
)
SELECT
    hd.ProcessName,
    hd.CurrentHour [HourWithDate],
    CONVERT(DATE, hd.CurrentHour) [Date],
    DATEPART(HOUR, hd.CurrentHour) [Hour],
    hd.Minutes
FROM HourlyData hd
ORDER BY
    hd.ProcessName,
    hd.CurrentHour;

上述示例的输出如下所示：

ProcessName HourWithDate            Date        Hour    Minutes
process1    2016-10-10 11:00:00.000 2016-10-10  11      10
process2    2016-10-10 11:00:00.000 2016-10-10  11      20
process2    2016-10-10 12:00:00.000 2016-10-10  12      30
process3    2016-10-10 22:00:00.000 2016-10-10  22      39
process3    2016-10-10 23:00:00.000 2016-10-10  23      60
process3    2016-10-11 00:00:00.000 2016-10-11  0       60
process3    2016-10-11 01:00:00.000 2016-10-11  1       60
process3    2016-10-11 02:00:00.000 2016-10-11  2       36

Answer 2

我觉得这很管用，但很难看。也许有人能做得更优雅些？

SELECT 
   case 
     when HOUR(starttime) < 11 AND HOUR(endtime) = 11  then minute(endtime)
     when HOUR(starttime) < 11 AND HOUR(endtime) > 11  then 60
     when HOUR(starttime) = 11 AND HOUR(endtime) = 11  then minute(endtime) - minute(starttime)
     when HOUR(starttime) = 11 AND HOUR(endtime) > 11  then 60 - minute(starttime)
     else 0
   end AS ProcessTimeHour_11,
    case 
     when HOUR(starttime) < 12 AND HOUR(endtime) = 12  then minute(endtime)
     when HOUR(starttime) < 12 AND HOUR(endtime) > 12  then 60
     when HOUR(starttime) = 12 AND HOUR(endtime) = 12  then minute(endtime) - minute(starttime)
     when HOUR(starttime) = 12 AND HOUR(endtime) > 12  then 60 - minute(starttime)
     else 0
   end AS ProcessTimeHour_12
from StuffAndThings

Answer 3

  SELECT CAST(DATEADD(MINUTE,DATEDIFF(MINUTE,'2016-10-10 11:10','2016-10-10 12:20'),'2011-01-01 00:00:00.000') AS TIME)
as timeDifference

用timeDifference -

SELECT CAST(DATEADD(MINUTE,DATEDIFF(MINUTE,StartTime,EndTime),'2011-01-01 00:00:00.000') AS TIME)
as timeDifference 
from #YourTableName

与Days和TimeDiffernece

    declare @start_time as varchar(150);
    declare @end_time as varchar(150);
    set @start_time='2016-10-10 10:10';
    set @end_time='2016-10-12 12:10'

    SELECT datediff(day,@start_time,@end_time) as dayDifference,
CAST(DATEADD(MINUTE,DATEDIFF(MINUTE,@start_time,@end_time),'2011-01-01 00:00:00') AS TIME(0))
 as timeDifference