Measuring time difference

rethca

This problem is driving me nuts. Every two hours (e.g. 4-6pm) I would like to measure the amount of time each of my guys spend doing a particular task. Some guys can do multiple tasks within a 2 hour period. Each time they change task, they submit the new task along with their names and a timesatmp to a mysql database. How do I measure the time difference per task for each person? Thank you in advance for your assistance.

mrhappiness

Why not spending your table another column called "taskend"
This column get's updated everytime a new task is beeing started.

If you say a task is over only if the person starts a new task he's working 24/7 isn't he?

kpzani

why not count the number of records with a start time within a set period

SELECT person, COUNT(starttime) AS notasks, DATE_FORMAT(starttime, "%d/%M/%Y H") as timeperiod FROM table GROUP BY timeperiod

that will give you each hour then just change it to an if with an even or odd check
something like

SELECT person, COUNT(starttime) AS notasks, CONCAT(DATE_FORMAT(starttime, "%d/%M/%Y"), DATE_FORMAT(starttime, "H") - (DATE_FORMAT(starttime, "H") MOD 2) as timeperiod FROM table GROUP BY timeperiod

rethca

I will try out these suggestions and let you know how it goes. Thanks again.