计算多个时间戳之间的差和

Question

我有一个表，其中列出了playerID以及一个startDate和endDate列。这些是时间戳。

例如：

playerID    startDate               endDate
1           2017-06-01 12:00:00     2017-06-01 12:05:00 
1           2017-06-01 13:30:00     2017-06-01 13:33:00 
1           2017-08-04 14:57:00     0000-00-00 00:00:00

我正在尝试运行一个查询，以获得特定播放器的startDate和endDate之间的总秒数。如果endDate是0000-00-00 00:00:00，那么我应该使用当前时间。

以下是我尝试过的：

select
    IF(endDate = '0000-00-00 00:00:00', CURRENT_TIMESTAMP, endDate) as finishDate, 
    sum((startDate-finishDate)) as timeTaken 
from table where playerID=1 group by playerID

这有两个问题。我不认为我可以使用finishDate，因为我得到一个未知的列错误。我还需要playerID 1的所有行的和

Answer 1

MySQL有一个方便的函数TIMESTAMPDIFF，它可以以不同的单位计算两个时间戳之间的差值，包括秒。我们可以简单地对每个玩家进行聚合，并将起始时间戳和结束时间戳之和，使用CASE表达式用当前时间戳替换(有点奇怪)的零时间戳。

SELECT
    playerID,
    SUM(TIMESTAMPDIFF(SECOND,
                      startDate,
                      CASE WHEN endDate = '0000-00-00 00:00:00'
                           THEN CURRENT_TIMESTAMP ELSE endDate END)) AS timeTaken
FROM yourTable
GROUP BY playerID

社论:我不知道0000-00-00 00:00:00在你的桌子上做什么，但我更愿意使用像NULL这样的东西来表示一个未知的结束时间戳。这也会使代码更好一些，例如，我们可以使用下面的代码来检查未知的结束时间戳：

COALESCE(endDate, CURRENT_TIMESTAMP)