# Working out the difference between the date/time in one row and the date/time in the row above

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

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

• Marked as answer by 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 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 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!

• Marked as answer by 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!

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