MySQL 计算两行之间的时间差
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7937288/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calculate the time difference between of two rows
提问by user1019444
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 等之间的时差是多少。我知道我需要在桌子上自我加入，但我没有得到正确的条款。
回答by Yahia
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 requestidis 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
回答by kalan nawarathne
The accepted answer is correct but gives the difference of numbers. As an example if I have the following 2 timestamps:
接受的答案是正确的，但给出了数字的差异。例如，如果我有以下 2 个时间戳：
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
回答by Jinav Shah
SELECT TIMESTAMPDIFF(SECOND, grd.startdate, grd1.startdate) as TD FROM myTable A
inner join myTable B on A.requestId = B.requestId - 1 and
A.startdate >= '2019-07-01' order by TD desc
