Sum time between dates in MySQL

Question

I have a MySQL table looking like this. It's basically a time sheet for tasks.

  id  |  task  |  user  |          start        |         stop          |  running
------+--------+--------+-----------------------+-----------------------+-----------
  1   |   12   |   2    |  2009-10-01 10:05:00  |  2009-10-01 14:23:00  |  0
  1   |   13   |   4    |  2009-10-05 08:58:00  |  2009-10-05 12:16:00  |  0
  1   |   14   |   3    |  2009-10-05 20:30:00  |  2009-10-05 22:00:00  |  0

I want to sum the time spent:

1) Today 2) Yesterday 3) This week 4) Last week 5) This month 6) Last month

I don't need the queries for each of theese, but just the first one to get me started. I'm not that familiar with different date functions in MySQL, and I think it's difficult to read and navigate through their documentation..

Answer 1

It's not THAT difficult to navigate through MySQL documentation.

You need TIMEDIFF() and TIME_TO_SEC functions.

select SUM(TIME_TO_SEC(TIMEDIFF(stop, start))) from my_table
  where start >= CURRENT_DATE() and stop <= CURRENT_DATE() + INTERVAL 1 DAY

will get you total time (in seconds) spent on tasks today. Look at DATE_SUB() if you're going to specify criteria for other ranges (e.g. last month, etc) in MySQL instead of supplying them as parameters to your query.

Answer 2

SELECT 'TODAY' Date, SUM(TIME_TO_SEC(TIME_DIFF(stop - start))) TimeSpent
FROM table1
WHERE start >= CURDATE()
  AND stop < CURDATE() + INTERVAL 1 DAY