计算两行之间的时间差。

I have a table with column StartDate, I want to calculate the time difference between two consecutive record.

我有一个带有列StartDate的表，我想计算两个连续记录之间的时间差。

Thanks.

谢谢。

@ Mark Byers and @ Yahia, I have request table as requestId, startdate

@ Mark Byers和@ Yahia，我的请求表是requestId, startdate

requestId    startdate               
1            2011-10-16 13:15:56
2            2011-10-16 13:15:59
3            2011-10-16 13:15:59
4            2011-10-16 13:16:02
5            2011-10-16 13:18:07

and i want to know what is the time difference between requestid 1 & 2, 2 & 3, 3 & 4 and so on. i know i will need self join on table, but i am not getting correct on clause.

我想知道requestid 1和2,2和3,3和4之间的时间差是多少。我知道我需要在桌子上进行自我连接，但是我没有得到正确的条款。

2 个解决方案

#1

To achieve what you are asking try the following (UPDATE after edit from OP):

要实现您的要求，请尝试以下操作(从OP编辑后更新):

SELECT A.requestid, A.starttime, (B.starttime - A.starttime) AS timedifference
FROM MyTable A INNER JOIN MyTable B ON B.requestid = (A.requestid + 1)
ORDER BY A.requestid ASC

IF requestid is not consecutive then you can use

如果requestid不是连续的，则可以使用

SELECT A.requestid, A.starttime, (B.starttime - A.starttime) AS timedifference
FROM MyTable A CROSS JOIN MyTable B
WHERE B.requestid IN (SELECT MIN (C.requestid) FROM MyTable C WHERE C.requestid > A.requestid)
ORDER BY A.requestid ASC

#2

The accepted answer is correct but gives the difference of numbers. As an example if I have the following 2 timestamps:

公认的答案是正确的，但给出的数字不同。举个例子，如果我有以下两个时间戳:

2014-06-09 09:48:15
2014-06-09 09:50:11

The difference is given as 196. This is simply 5011 - 4815. In order to get the time difference, you may modify the script as follows:

差是196。这就是5011 - 4815。为了得到时差，您可以修改脚本如下:

SELECT A.requestid, A.starttime, TIMESTAMPDIFF(MINUTE,A.starttime,B.starttime) AS timedifference 
FROM MyTable A INNER JOIN MyTable B ON B.requestid = (A.requestid + 1) 
ORDER BY A.requestid ASC

#1