选择两行中两个unix时间戳之间的差异
问题描述:
我在MySQL中有一个表格,包含开始和停止时间。选择两行中两个unix时间戳之间的差异
我想锻炼从停止到下一次开始的时间。
我已经做了sqlfiddle我的表 http://sqlfiddle.com/#!2/2c043/1
ID start_time end_time
410 1367894268 1367898275
409 1367893164 1367894268
408 1367888257 1367893153
407 1367837969 1367888247
,所以我想要做的是什么
end_time from row 410 - the start_time from row 409
1367898275 - 1367893164
和像
end_time start_time time_duration
1367898275 1367893164 5111
感谢您的帮助输出。
答
TRY像下面,它会帮助你
SQL FIDDLE:http://sqlfiddle.com/#!2/2c043/26
SELECT
A.idtrip_data,
A.start_time,
B.end_time,
(B.end_time - a.start_time) AS timedifference
FROM myTable A
INNER JOIN myTable B
ON B.idtrip_data = (A.idtrip_data + 1)
ORDER BY A.idtrip_data ASC
答
要实现你问什么尝试以下操作:
SELECT (B.start_time - A.end_time) AS time_duration
FROM MyTable A INNER JOIN MyTable B ON B.ID = (A.ID + 1)
ORDER BY A.ID ASC
IF ID
是不连续的,那么你可以使用
SELECT (B.start_time - A.end_time) AS time_duration
FROM MyTable A CROSS JOIN MyTable B
WHERE B.ID IN (SELECT MIN (C.ID) FROM MyTable C WHERE C.ID > A.ID)
ORDER BY A.ID ASC
+0
+1看起来被删除的行 – tgkprog 2013-05-07 06:16:58
如果不是顺序的话就不会工作 – tgkprog 2013-05-07 06:23:57