CODEWITHSUNDEEP
sqlsql-server-2012
Charls
Charls

Reputation: 15

SQL Server 2012 query to calculate date and time difference

Hello I need to calculate the time difference on this timestamp transaction column, the format is as follows, I was triying to use the DATEDIFF function to no avail. Thanks for your help.

Txtimestamp
2016-01-05 12:16:51.000
2016-01-05 12:16:51.000
2016-01-18 12:24:16.000
2016-01-18 12:24:16.000
2016-01-20 08:15:32.000
2016-01-20 08:15:32.000
2016-01-20 12:24:29.000
2016-01-20 12:24:29.000
2016-01-29 12:18:11.000
2016-01-29 12:18:11.000
2016-02-01 12:16:43.000
2016-02-01 12:16:43.000

Upvotes: 0

Views: 69

Answers (2)

Pat Jones
Pat Jones

Reputation: 896

I had something like this in mind, but again it is not as crisp as Gordon's answer. I'm not sure what the performance difference would be. Probably a lot since this needs to perform two sub-queries...

 SELECT tA.txtimestamp, tB.txtimestamp, DATEDIFF(second, tA.txtimestamp, tB.txtimestamp) AS diff
 FROM (SELECT row_num = ROW_NUMBER() OVER (ORDER BY txtimestamp), txtimestamp
       FROM your_table) AS tA 
       INNER JOIN
      (SELECT row_num = ROW_NUMBER() OVER (ORDER BY txtimestamp), txtimestamp
       FROM your_table) AS tB ON tA.row_num = tB.row_num - 1

It is clunky, but certainly another possibility. You would need to modify the "-" in the join condition to a "+" depending on how you want the difference to come out.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270523

Do you want something like this?

select datediff(second, lag(txtimestamp) over (order by txtimestamp), txtimestamp) as diff_in_seconds
from t;

Upvotes: 3

Related Questions