如何从timestamp-sql中以分钟为单位求和唯一的总duarion

Question

|Client1    |   2017-01-01 08:00:00      |   2017-01-01 08:05:00|
|Client1    |   2017-01-01 08:00:00      |   2017-01-01 08:10:00|

Select ClientName, SUM(date_trunc('second',coalesce(EndTime,now()::timestamp(0))-StartTime)) as duration 
from table 
group by ClientName

Answer 1

请参考计算实际停机时间忽略日期/时间的重叠

DECLARE @clientUsage TABLE (
    ID INT PRIMARY KEY NOT NULL IDENTITY(1,1),
    ClientName VARCHAR(25), 
    StartTime  DATETIME,
    EndTime  DATETIME
)

INSERT @clientUsage (ClientName, StartTime, EndTime) VALUES 
     ('client', '2014-11-20 17:31:01.467', '2014-11-20 18:01:01.243') 
    ,('client', '2014-11-28 17:59:00.987', '2014-11-28 18:09:02.167')
    ,('client', '2014-11-28 18:00:01.403', '2014-11-28 18:25:01.443') 
    ,('client', '2014-11-29 19:13:08.580', '2014-11-30 05:30:01.763') 
    ,('client', '2014-11-30 01:55:01.953', '2014-11-30 03:54:01.730')
    ,('client 2', '2014-12-19 23:09:01.303', '2014-12-22 09:43:01.397')
    ,('client 2', '2014-12-19 23:09:01.303', '2014-12-22 09:43:01.397')
    ,('client 2', '2014-12-19 23:09:01.303', '2014-12-22 09:43:01.397') 
    ,('client 2', '2014-12-19 23:09:01.303', '2014-12-22 09:43:01.397')
    ,('client 2', '2014-12-19 23:09:01.303', '2014-12-22 09:43:01.397') 

如果使用表而不是临时表

select clientname,sum(actual) from
(SELECT
    clienttime.ClientName,
    clienttime.StartTime,
    clienttime.EndTime,
    COALESCE(Actual, 0) AS Actual
FROM @clientUsage clienttime
    LEFT OUTER JOIN (
        SELECT DISTINCT
            D1.ClientName,
            MIN(CASE WHEN D1.StartTime < D2.StartTime THEN D1.ID ELSE D2.ID END) AS [ID],
            MIN(CASE WHEN D1.StartTime < D2.StartTime THEN D1.StartTime ELSE D2.StartTime END) AS [StartTime],
            MAX(CASE WHEN D1.EndTime > D2.EndTime THEN D1.EndTime ELSE D2.EndTime END) AS [EndTime],
            DATEDIFF(MINUTE,
                MIN(CASE WHEN D1.StartTime < D2.StartTime THEN D1.StartTime ELSE D2.StartTime END),
                MAX(CASE WHEN D1.EndTime > D2.EndTime THEN D1.EndTime ELSE D2.EndTime END)) AS Actual
        FROM @clientUsage D1
            INNER JOIN @clientUsage D2
                ON D1.ClientName = D2.ClientName
                    AND (D1.StartTime BETWEEN D2.StartTime AND D2.EndTime
                        OR D2.StartTime BETWEEN D1.StartTime AND D1.EndTime)
        GROUP BY
            D1.ClientName,
            D1.StartTime
    ) Outages
        ON Outages.ID = clienttime.ID) op group by clientname

我在sql Server中进行了测试。