当前位置: 动力学知识库 > 问答 > 编程问答 >

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

网友答案:

Do you want something like this?

select datediff(second, lag(txtimestamp) over (order by txtimestamp), txtimestamp) as diff_in_seconds
from t;
网友答案:

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.

分享给朋友:
您可能感兴趣的文章:
随机阅读: