Calculating sum of difference between multiple timestamps

Question

I have a table that lists playerID along with a startDate and endDate column. These are timestamps.

e.g.

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

I am trying to run a query to get the total number of seconds between the startDate and endDate for a specific player. If the endDate is 0000-00-00 00:00:00 then I should use the current time.

Here is what I have tried:

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

This has two issues. I don't think I can use finishDate as I get an unknown column error. I also want the sum of all rows for playerID 1

Tim Biegeleisen · Accepted Answer · 2017-09-04T14:14:47.147

MySQL has a handy function TIMESTAMPDIFF which can calculate the difference between two timestamps in a variety of units, including seconds. We can simply aggregate over each player and sum the diff of the starting and ending timetamps, using a CASE expression to replace the (somewhat odd) zero timestamp with the current timestamp.

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

Editorial: I don't know what 0000-00-00 00:00:00 is doing in your table but I would much rather use something like NULL to represent an end timestamp which is not known. This would also make the code a bit nicer, e.g. we could use the following to check for an unknown end timestamp:

COALESCE(endDate, CURRENT_TIMESTAMP)

Calculating sum of difference between multiple timestamps

1 Answers1