Reputation: 6740
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..
Upvotes: 1
Views: 4852
Reputation: 100831
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.
Upvotes: 3
Reputation: 47968
SELECT 'TODAY' Date, SUM(TIME_TO_SEC(TIME_DIFF(stop - start))) TimeSpent
FROM table1
WHERE start >= CURDATE()
AND stop < CURDATE() + INTERVAL 1 DAY
Upvotes: 1