Sum datediff in minutes with MySQL

Question

I have a table of tasks where I enter when I started a task tStart and when I ended the task tEnd. I would like to know how many minutes of work I spent on tasks over a period of time. So whats wrong with this query?

SELECT SUM(MINUTE(DATEDIFF(tEnd, tStart)))
FROM tasks
WHERE tStart >= '2011-04-04'
    AND tEnd <= '2011-04-04'

Answer 1

Your condition

WHERE tStart >= '2011-04-04' AND tEnd <= '2011-04-04'

Pretty much ensures that the only records that will be left are where

tStart = tEnd = '2011-04-04'

It goes without saying that DATEDIFF between them gives 0, for a grand total sum of 0. In fact, DATEDIFF ignores the time portion, so you need to use TIMEDIFF + TIME_TO_SEC

SELECT SUM(TIME_TO_SEC(TIMEDIFF(tEnd, tStart))/60)
FROM tasks
WHERE tStart >= '2011-04-04' AND tEnd <= adddate('2011-04-04', interval 1 day)

Notice the change to the WHERE clause. If you are trying to say, started any time today and ended any time today, then the range should be (already assuming start < end)

WHERE tStart >= '2011-04-04'
  AND tEnd < adddate('2011-04-04', interval 1 day)

You can hard code it to 2011-04-05, but using < (less than) and adding one day to the end limit is what I am showing here.