How to sum UNIQUE total duarion in minutes from timestamp- sql

Question

I have following case scenario:

My table has following columns: ID, ClientName(String), StartTime (timestamp), EndTime (timestamp).

My goal is to sum duration for each Client e.g. for the last day, assuming that in one time period can be few values and I must have only real duration.

e.g. Content of table:

|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|

I am using following query for this example:

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

Result of above query is 15 but is should be 10. The same period of the time shouldn't be calculate.

Any ideas how to get correct results in PostgreSQL?

@ZoharPeled Nitpicking: [Use Postgres instead of Postgre](http://stackoverflow.com/tags/postgresql/info) — Vivek S., Jan 02 '17 at 11:50
Output should be: Sum of total duration group by client name. — route00, Jan 04 '17 at 10:08

user3600910 · Answer 1 · 2017-01-02T14:47:13.823

0

you didn't mention which database your are using , the implementation for each one of them is different, but with the same logic,

for Postgresql i use row_number to remove the incorrect rows

    select ClientName,
           SUM(date_trunc('second',coalesce(EndTime,now()::timestamp(0))-StartTime)) as duration,
           SUM(datediff(minute,StartTime,EndTIme)) as Duration2
    from(
    select ClientName,
           StartTime,
           EndTIme,
           row_number () over ( partition by ClientName,StartTime order by EndTime desc) as rank
    from table) a
    where rank=1
    group by ClientName

edited Jan 02 '17 at 14:47

answered Jan 02 '17 at 11:50

user3600910

2,839
4
22
36

I think there is not "wrong" rows, just overlapping rows – MtwStark Jan 02 '17 at 12:09
I am using PostgreSQL. Executed your example but it giving me error: {{ ERROR: syntax error at or near "end"}} – route00 Jan 02 '17 at 13:02
try know ,i've added the end by mistake – user3600910 Jan 02 '17 at 13:50
Thanks, I checked and now I have syntax error: ERROR: syntax error at or near "table" – route00 Jan 02 '17 at 14:00
put your table name, and remove the alias – user3600910 Jan 02 '17 at 14:04
When I have following data in my table `1 Test 2017-01-01 10:40:00 2017-01-01 10:50:00 2 Test 2017-01-01 10:45:00 2017-01-01 10:48:00` it shows 13 minutes, which is incorrect. Should be 10 minutes. – route00 Jan 02 '17 at 14:24
very strange,pls try it know , i've changed the StartDate to EndTime in the order by, also you can use datediff to calculate the duration, try sum(datediff(minute,StartTime,EndTime) – user3600910 Jan 02 '17 at 14:44
Unfortunately *datediff* doesn't work. ERROR: column "minute" does not exist. I tried using apostrophe for word minute but doesn't work. – route00 Jan 02 '17 at 15:01

score 0 · Answer 2 · edited May 23 '17 at 11:53

Try this from reference Calculate Actual Downtime ignoring overlap in dates/times

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')

If you use your table instead of temp table

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

I tested in sql server.

How to sum UNIQUE total duarion in minutes from timestamp- sql

2 Answers2