CODEWITHSUNDEEP
sqlsql-server
Musakkhir Sayyed
Musakkhir Sayyed

Reputation: 7170

How to Calculate Cumulative time difference in sql

I want to display cumulative time difference in sql. Here is the timestamp column. The time difference should be in cumulative form without using temp table but for id we can use ROW_NUMBER():

Timestamp
2015-05-20 11:23:34.853
2015-05-20 11:21:40.790
2015-05-20 11:20:12.447
2015-05-20 11:19:31.723
2015-05-20 11:19:10.993

The result should be like this:

TimeDifference
00:00:00
00:01:54
00:01:28
00:00:41
00:00:21

Upvotes: 2

Views: 1189

Answers (2)

ughai
ughai

Reputation: 9890

You can use DATEDIFF with CONVERT to Time and a self join based on order of timestamp to achieve this. Something like this (for SQL Server 2008).

;WITH cte as 
(
select '2015-05-20 11:23:34.853' ts
union all select '2015-05-20 11:21:40.790'
union all select '2015-05-20 11:20:12.447'
union all select '2015-05-20 11:19:31.723'
union all select '2015-05-20 11:19:10.993'
), cte2 as 
(
SELECT *,ROW_NUMBER()OVER(ORDER BY ts DESC) rn
FROM cte
)
select t1.ts,t2.ts,CONVERT(TIME,DATEADD(s,ISNULL(DATEDIFF(s,t1.ts,t2.ts),0),0)) diff
from cte2 t1 LEFT JOIN cte2 t2 on t1.rn = t2.rn + 1

Output

ts  ts  diff
2015-05-20 11:23:34.853 NULL    00:00:00.0000000
2015-05-20 11:21:40.790 2015-05-20 11:23:34.853 00:01:54.0000000
2015-05-20 11:20:12.447 2015-05-20 11:21:40.790 00:01:28.0000000
2015-05-20 11:19:31.723 2015-05-20 11:20:12.447 00:00:41.0000000
2015-05-20 11:19:10.993 2015-05-20 11:19:31.723 00:00:21.0000000

If you were using SQL 2012, you could have used LAG / LEAD like this

SELECT CONVERT(TIME,DATEADD(s,ISNULL(DATEDIFF(s,ts,LAG(ts)over(order by ts DESC)),0),0))
FROM cte
ORDER BY ts DESC

Upvotes: 4

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

You can number your rows and then with left join calc the difference:

DECLARE @t TABLE ( d DATETIME )

INSERT  INTO @t
VALUES  ( '2015-05-20 11:23:34.853' ),
        ( '2015-05-20 11:21:40.790' ),
        ( '2015-05-20 11:20:12.447' ),
        ( '2015-05-20 11:19:31.723' ),
        ( '2015-05-20 11:19:10.993' );

WITH    cte
          AS ( SELECT   d ,
                        ROW_NUMBER() OVER ( ORDER BY d DESC ) AS rn
               FROM     @t
             )
    SELECT  CAST(ISNULL(c2.d - c1.d, 0) AS TIME(0)) TimeDifference
    FROM    cte c1
            LEFT JOIN cte c2 ON c2.rn + 1 = c1.rn

Output:

TimeDifference
00:00:00
00:01:54
00:01:28
00:00:41
00:00:21

But be aware that time type can hold up to 24 hours.

Upvotes: 1

Related Questions