5

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'
JYelton
  • 35,664
  • 27
  • 132
  • 191
user520300
  • 1,497
  • 5
  • 24
  • 46

1 Answers1

7

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.

RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • so how do i get total number of minutes for a single day 2011-04-04 or lets say a week 2011-03-12 to 2011-03-27 ? – user520300 Apr 05 '11 at 00:54
  • This still results in 0 SELECT SUM(MINUTE(DATEDIFF(tEnd, tStart))) FROM tasks WHERE tStart >= '2011-04-04' AND tEnd < adddate('2011-04-04', interval 1 day) – user520300 Apr 05 '11 at 01:00
  • @User / Ah.. just realised DATEDIFF **ignores** the time portion, so you get 0 days. You wanted TIMEDIFF + TIME_TO_SEC – RichardTheKiwi Apr 05 '11 at 01:23