I have a table that is a project. Each record is a task in the
project. One field in each record is a date/time stamp for when that
task was completed. I need to be able to: 1) compute how much time has
passed between each date/time stamp and 2) compute how much time has
passed between the first and last date/time stamp (total project time).
This involves a self join, and I have nearly gotten it to work, but I
need a little more help.
Here is an example of the "selfJoin" table:
task_ID task date_time
1 go to grocery store 2004-12-01 15:53:55
2 cook food 2004-12-03 12:00:00
3 serve food 2004-12-03 18:00:00
Here is the best SQL statement I can come up with:
$sql = "SELECT first.task_ID, first.task, first.date_time,
UNIX_TIMESTAMP(second.date_time) - UNIX_TIMESTAMP(first.date_time)
FROM selfJoin AS first, selfJoin AS second
WHERE first.task_ID < second.task_ID";
Here is what I want the result to look like:
task_ID task date_time total time
1 go to grocery store 2004-12-01 15:53:55
2 cook food 2004-12-03 17:00:00 197065 seconds
3 serve food 2004-12-03 18:00:00 3600 seconds
Total project time: 201965 seconds
Except my SQL statement doesn't produce this. (I have PHP functions to
reformat the times to a nice format.)
What SQL statement determines the difference between a task's date_time
value and the one before it? Ie, how long each task took?
Thanks,
-Andrew K.