Computing average time given set of start and end dates

I have a table with a field called date_start and a field called date_end The values of the field are like this

year-month-day hours:minutes:seconds

I need to find out:

• How m开发者_如何学运维uch time it to for the person to complete the task.
• The average time it took of all of them.

I'm still a bit new and honestly have no idea where to even start with this.

Thanks for all the help.

---

Assuming the fields in MySQL are TIMESTAMP or DATETIME, i'd use two MySQL queries. One for the task specific results.

SELECT UNIX_TIMESTAMP(date_end) - UNIX_TIMESTAMP(date_start)  AS seconds_to_complete_task
FROM a

One for the average result.

SELECT AVG(UNIX_TIMESTAMP(date_end)-UNIX_TIMESTAMP(date_start)) AS avg_seconds_to_complete_task
FROM a

---

Start here: strtotime() This function will convert your times to a number representing the number of seconds since 1970. You can then subtract one from the other to find elapsed seconds.

http://us3.php.net/strtotime

---

I haven't tested it but this would work:

SELECT (UNIX_TIMESTAMP(date_end) - UNIX_TIMESTAMP(date_start)) AS personal_complete_time, 
AVG(UNIX_TIMESTAMP(date_end) - UNIX_TIMESTAMP(date_start)) AS total_average 
FROM mytable

You'll get average number of seconds to complete

---

You say you have a table with dates stored in this format:year-month-day hours:minutes:seconds.

Are you sure you're not using a datetime field? If so, that solves half your problem. Datetime fields are represented in this manner.

---

This will work:

$total_time_worked = 0;
foreach( $db_records as $time_entry )
{
    $start = strtotime($time_entry['date_start']);
    $end = strtotime($time_entry['date_end']);

    // Time worked (in seconds)
    $worked = $end-$start;

    // Increment the total
    $total_time_worked += $worked;
}

// Get the average time worked (in seconds)
$average = $total_time_worked/count($db_records);