Calculate difference between two timestamps in SQL in the same table

Question

I have a table that the columns are:

ID   UserId Serial ModifiedDate
---- ------ ----- ----------------

I need to calculate de difference between timestamp from each row. For example if i have the table like this:

ID   UserId Serial ModifiedDate
---- ------ ----- ----------------
001    1     1111  2015-07-20 10:56:53.0000000
002    1     1111  2015-07-21 18:49:24.0000000
003    1     1111  2015-07-22 08:49:23.0000000

I need to make the difference between the timestamp from 001 and 002, then from 002 and 003 and the result need to be like:

ID   UserId Serial ModifiedDate                Difference 
---- ------ ----- ----------------             --------
001    1     1111  2015-07-20 10:56:53.0000000 
002    1     1111  2015-07-21 18:49:24.0000000
003    1     1111  2015-07-22 08:49:23.0000000

I trying to work with cursors to do this but I couldn’t find the way to have the result of the difference in an new alias column.

This is my query:

DECLARE @id bigint, @lastmodif datetime2(7), @id2 bigint, @lastmodif2 datetime2(7),@total int;

DECLARE status_cursor CURSOR FOR 

SELECT [Id], [ModifiedDate], '0' AS Difference 
FROM [Registrations]
  where p.Serial = 1111  
  Order by ModifiedDate

OPEN status_cursor

DECLARE status_cursor2 CURSOR FOR 

SELECT [Id], [ModifiedDate],'0' AS Difference 
FROM [Registrations]
  where p.Serial = 1111  
  Order by ModifiedDate

OPEN status_cursor2

FETCH NEXT FROM status_cursor2

WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM status_cursor INTO @regid, @lastmodif
FETCH NEXT FROM status_cursor2 INTO @regid2, @lastmodif2
SET @total = 
        (
           DATEDIFF(second,@lastmodif,@lastmodif2)
        )
UPDATE status_cursor SET Result = @total

Solution 1

As per your comment, your SQL Server version is 2012. So you can do this by LEAD()

You need to Replace HH with desired value

SELECT ID,
       UserId,
       Serial,
       ModifiedDate,
       DATEDIFF(HH,ModifiedDate,LEAD(ModifiedDate) over(ORDER BY ID)) AS [Difference]
FROM Times

Try below query if LAG() doesnt work on your DB because of configuration issues.

WITH CTE AS
(
    SELECT *,ROW_NUMBER() OVER(ORDER BY CAST(ID AS INT)) AS RN
    FROM times
)

SELECT C1.*,DATEDIFF(HH,C1.MODIFIEDDATE,C2.MODIFIEDDATE) AS [DIFFERENCE]
FROM CTE C1 LEFT JOIN CTE C2 ON C1.RN+1 = C2.RN

Solution 2

for sql server 2008 and higher

with cte as 
(
    select 1 as Id , 1 as UserID, 1111 as Serial, ' 2015-07-20 10:56:53.0000000' as ModifiedDate
    union all 
    select 
    002,    1,     1111 , '2015-07-21 18:49:24.0000000'
    union all
    select 
    003,    1,     1111,  '2015-07-22 08:49:23.0000000'
)
,
cte2 as (
select *, 
        (select min(ModifiedDate) from cte as nextRow where nextRow.Serial = cte.Serial and nextRow.Id > cte.Id)  as NextModifiedDate

    from 
    cte 
)
select id,USERid,Serial,ModifiedDate,DATEDIFF(SECOND,ModifiedDate,isnull(NextModifiedDate,ModifiedDate)) as Difference 
 from cte2

Also, you can use this directly with your table

    select * 
            -- ,(select min(ModifiedDate) from Registrations as nextRow where nextRow.Serial = Registrations.Serial and nextRow.Id > Registrations.Id)  as NextModifiedDate
            , DATEDIFF(second,Registrations.ModifiedDate, 
                        isnull((select min(ModifiedDate) from Registrations as nextRow where nextRow.Serial = Registrations.Serial and nextRow.Id > Registrations.Id),Registrations.ModifiedDate)
                       ) as Difference 
        from 
        Registrations 

Comments

[OSAMA ORABI]

also, you can use this select * -- ,(select min(ModifiedDate) from Registrations as nextRow where nextRow.Serial = Registrations.Serial and nextRow.Id > Registrations.Id) as NextModifiedDate , DATEDIFF(second,Registrations.ModifiedDate, isnull((select min(ModifiedDate) from Registrations as nextRow where nextRow.Serial = Registrations.Serial and nextRow.Id > Registrations.Id),Registrations.ModifiedDate) ) as Difference from Registrations

[Mitch Wheat]

He did. Because I asked! ;)

[juanfandres]

I tried to use LEAD but i have this message: The Parallel Data Warehouse (PDW) features are not enabled.

[juanfandres]

I tried to use the query directly in the table but when i have a difference between 2 timestamp like this: 2015-07-22 08:49:23.0000000 - 2015-07-22 08:50:08.0000000 i have negative difference and is not representative the number

[juanfandres]

i solved the issue with negative difference. Now i have the problem when i have 2 equals timestamps. In the difference column i have te same number. For example: i have this 3 rows 2015-07-26 22:42:03.0000000 / 2015-07-26 22:42:03.0000000 / 2015-07-27 07:07:16.0000000. As you see the first 2 are equals so in the difference column i have the same number for the first 2 that is the difference between the 2nd one and the 3er one.

[Mitch Wheat]

database compatability level?