MySQL - 查找不同 ID 的行之间的时间差

Question

我有一个包含以下数据的表格:

ID   status-before             status-current         DATE-timestamp         
5    Un-Assigned                Assigned              2013-12-17 14:14:04
5    Assigned                   Completed             2013-12-21 14:14:04
7    Un-Assigned                Assigned              2014-01-02 14:14:04 
7    Assigned                   Completed             2014-01-21 14:14:04
7    Completed                  Locked                2014-01-21 14:14:04
8    Un-Assigned                Assigned              2014-12-21 14:14:04
8    Assigned                   Completed             2014-12-27 14:14:04

我使用 group by 对 id 进行分组并对时间戳字段进行排序。我不知道如何使用 rownum(或任何其他方法)来计算仅具有相同 ID 的行的时间差。

我希望我的最终输出如下

ID   status-before              status-current     DATE-timestamp       Time Diff(Hours)
5    Un-Assigned                Assigned        2013-12-17 14:14:04    xx
5    Assigned                   Completed       2013-12-21 14:14:04    xx
7    Un-Assigned                Assigned        2014-01-02 14:14:04    xx 
7    Assigned                   Completed       2014-01-21 14:14:04    xx
7    Completed                  Locked          2014-01-21 14:14:04    xx
8    Un_Assigned                Assigned        2014-12-21 14:14:04    xx
8    Assigned                   Completed       2014-12-27 14:14:04    xx

Answer 1

使用 TIMEDIFF 返回 hours:minutes:seconds 或使用 DATEDIFF 返回 days

SELECT TIMEDIFF('complete.TIMESTAMP','assign.TIMESTAMP');
SELECT DATEDIFF('complete.TIMESTAMP','assign.TIMESTAMP');