locked
Working out the difference between the date/time in one row and the date/time in the row above RRS feed

  • Question

  • Hi

    I have a table that has a unique ID and a datetime of when something changed.

    See example:

    ID    TimeStamp
    16094    2013-11-25 11:46:38.357
    16095    2013-11-25 11:46:38.430
    16096    2013-11-25 11:46:38.713
    16097    2013-11-25 11:46:38.717
    16098    2013-11-25 11:46:38.780
    16099    2013-11-25 11:46:38.783
    16100    2013-11-25 11:46:38.850
    16101    2013-11-25 11:46:38.850
    16102    2013-11-25 11:46:38.927
    16103    2013-11-25 11:46:38.927
    16104    2013-11-25 11:46:38.987
    16105    2013-11-25 11:46:38.990
    16106    2013-11-25 11:46:39.047

    Is there a way I can calculate the difference between row 16106 and 16105 and enter it in line 10601.

    Struggling at the moment.

    Thanks

    Aaron

    Thursday, May 21, 2015 8:15 AM

Answers

  • Try this code:

    SELECT T1.ID, DATEDIFF ( MILLISECOND , T1.TimeStamps, T2.TimeStamps)/1000.0 AS Difference_In_Seconds
     FROM #tmpX T1,
    ( SELECT id, timestamps FROM #tmpX ) T2
    WHERE T1.ID = T2.ID-1
    

    You need to change Column Names & Table Name in the above as per your environment


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    • Marked as answer by AJCB Thursday, May 21, 2015 10:48 AM
    Thursday, May 21, 2015 8:49 AM
  • I suggest using a windowing function and the OVER clause for this. Borrowing some of Kunal's DDL:

    DECLARE @T TABLE
    (ID int, TimeStampCol DATETIME)
    insert into @T 
    SELECT 16094,'2013-11-25 11:46:38.357'	UNION ALL 
    SELECT 16095,'2013-11-25 11:46:38.430'	UNION ALL 
    SELECT 16096,'2013-11-25 11:46:38.713'	UNION ALL 
    SELECT 16097,'2013-11-25 11:46:38.717'	UNION ALL 
    SELECT 16098,'2013-11-25 11:46:38.780'	UNION ALL 
    SELECT 16099,'2013-11-25 11:46:38.783 '
    
    SELECT 
     *
    ,DATEDIFF(MILLISECOND, LAG(TimeStampCol, 1, NULL) OVER(ORDER BY ID), TimeStampCol)/1000.00 AS TimeDifferenceinSeconds
    FROM @t
    



    Tibor Karaszi, SQL Server MVP | web | blog

    Thursday, May 21, 2015 9:10 AM

All replies

  • What do you mean as enter it in line 10601? I mean are you trying to do a recursive operation or just you want to the calculation only once?
    Thursday, May 21, 2015 8:22 AM
  • A recursive operation for each row.  I am also wanting the answer in secconds.

    I am just learning tsql so bear with me...
    • Edited by AJCB Thursday, May 21, 2015 8:24 AM
    Thursday, May 21, 2015 8:23 AM
  • Basically I want to show it like this:

    ID           TimeStamp                          Seconds Diff
    16094    2013-11-25 11:46:38.357   0.073
    16095    2013-11-25 11:46:38.430   0.283
    16096    2013-11-25 11:46:38.713   0.004
    16097    2013-11-25 11:46:38.717   0.063
    16098    2013-11-25 11:46:38.780   0.063
    16099    2013-11-25 11:46:38.783   0.003
    16100    2013-11-25 11:46:38.850   0.067
    16101    2013-11-25 11:46:38.850   0
    16102    2013-11-25 11:46:38.927   0.077
    16103    2013-11-25 11:46:38.927   0
    16104    2013-11-25 11:46:38.987   0.06
    16105    2013-11-25 11:46:38.990   0.003
    16106    2013-11-25 11:46:39.047   0.057

    Thursday, May 21, 2015 8:37 AM
  • Basically I want to show it like this:

    ID           TimeStamp                          Seconds Diff
    16094    2013-11-25 11:46:38.357   0.073
    16095    2013-11-25 11:46:38.430   0.283
    16096    2013-11-25 11:46:38.713   0.004
    16097    2013-11-25 11:46:38.717   0.063
    16098    2013-11-25 11:46:38.780   0.063
    16099    2013-11-25 11:46:38.783   0.003
    16100    2013-11-25 11:46:38.850   0.067
    16101    2013-11-25 11:46:38.850   0
    16102    2013-11-25 11:46:38.927   0.077
    16103    2013-11-25 11:46:38.927   0
    16104    2013-11-25 11:46:38.987   0.06
    16105    2013-11-25 11:46:38.990   0.003
    16106    2013-11-25 11:46:39.047   0.057


    DECLARE @T TABLE
    (ID int, TimeStampCol DATETIME)
    insert into @T 
    SELECT 16094,'2013-11-25 11:46:38.357'	UNION ALL 
    SELECT 16095,'2013-11-25 11:46:38.430'	UNION ALL 
    SELECT 16096,'2013-11-25 11:46:38.713'	UNION ALL 
    SELECT 16097,'2013-11-25 11:46:38.717'	UNION ALL 
    SELECT 16098,'2013-11-25 11:46:38.780'	UNION ALL 
    SELECT 16099,'2013-11-25 11:46:38.783 '
    
    SELECT * FROM @T 
    
    select a.*,TimeDifferenceinSeconds = DATEDIFF(MILLISECOND,a.TimeStampCol,b.TimeStampCol)/1000.00 from @T a 
    inner join @T b 
    	ON a.ID + 1 = b.ID
    Is that you lookin for

    Hope that helps ... Kunal

    • Proposed as answer by ryguy72 Wednesday, May 27, 2015 3:52 PM
    Thursday, May 21, 2015 8:45 AM
  • Kind of but this is going to be an ongoing query that will be ran numerous time with the data changing so referencing the actual row numbers would not work.

    Thursday, May 21, 2015 8:47 AM
  • Try this code:

    SELECT T1.ID, DATEDIFF ( MILLISECOND , T1.TimeStamps, T2.TimeStamps)/1000.0 AS Difference_In_Seconds
     FROM #tmpX T1,
    ( SELECT id, timestamps FROM #tmpX ) T2
    WHERE T1.ID = T2.ID-1
    

    You need to change Column Names & Table Name in the above as per your environment


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    • Marked as answer by AJCB Thursday, May 21, 2015 10:48 AM
    Thursday, May 21, 2015 8:49 AM
  • Try this code:

    SELECT T1.ID, DATEDIFF ( MILLISECOND , T1.TimeStamps, T2.TimeStamps)/1000.0 AS Difference_In_Seconds
     FROM #tmpX T1,
    ( SELECT id, timestamps FROM #tmpX ) T2
    WHERE T1.ID = T2.ID-1

    You need to change Column Names & Table Name in the above as per your environment


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    Thursday, May 21, 2015 8:49 AM
  • I suggest using a windowing function and the OVER clause for this. Borrowing some of Kunal's DDL:

    DECLARE @T TABLE
    (ID int, TimeStampCol DATETIME)
    insert into @T 
    SELECT 16094,'2013-11-25 11:46:38.357'	UNION ALL 
    SELECT 16095,'2013-11-25 11:46:38.430'	UNION ALL 
    SELECT 16096,'2013-11-25 11:46:38.713'	UNION ALL 
    SELECT 16097,'2013-11-25 11:46:38.717'	UNION ALL 
    SELECT 16098,'2013-11-25 11:46:38.780'	UNION ALL 
    SELECT 16099,'2013-11-25 11:46:38.783 '
    
    SELECT 
     *
    ,DATEDIFF(MILLISECOND, LAG(TimeStampCol, 1, NULL) OVER(ORDER BY ID), TimeStampCol)/1000.00 AS TimeDifferenceinSeconds
    FROM @t
    



    Tibor Karaszi, SQL Server MVP | web | blog

    Thursday, May 21, 2015 9:10 AM
  • Simply...

    CREATE TABLE FORUM4 (ID INT, TIMES DATETIME2)
    INSERT INTO FORUM4 VALUES ('16094','2013-11-25 11:46:38.357'),
    ('16095','2013-11-25 11:46:38.430'),
     ('16096','2013-11-25 11:46:38.713'),
     ('16097','2013-11-25 11:46:38.717'),
     ('16098','2013-11-25 11:46:38.780'),
     ('16099','2013-11-25 11:46:38.783'),
     ('16100','2013-11-25 11:46:38.850'),
     ('16101','2013-11-25 11:46:38.850'),
     ('16102','2013-11-25 11:46:38.927'),
     ('16103','2013-11-25 11:46:38.927'),
     ('16104','2013-11-25 11:46:38.987'),
     ('16105','2013-11-25 11:46:38.990'),
     ('16106','2013-11-25 11:46:39.047')
    
    
    SELECT F.ID, F.TIMES, DATEDIFF(millisecond, F.TIMES, N.TIMES) /10000.0000
    FROM FORUM4 F
    LEFT OUTER JOIN FORUM4 N
    ON F.ID=N.ID-1 order by id

    Please mark as answer if this post helped you

    Thursday, May 21, 2015 9:21 AM