Average calculation time given by a set of start and end dates

Question

I have a table with a field date_startand a field called date_endField values ​​are similar to this

year-month-day hours:minutes:seconds

I need to find out:

• How long does it take for a person to complete a task.
• The average time spent by all of them.

I'm still a little newbie and honestly have no idea where to start from this.

Thanks for the help.

Answer 1

Assuming the fields in MySQL are TIMESTAMP or DATETIME, I would use two MySQL queries. One for specific tasks.

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

Answer 2

: strtotime() , 1970 . , .

http://us3.php.net/strtotime

Answer 3

I have not tested it, but it 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 will get the average number of seconds to complete

Answer 4

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

Are you sure you are not using the datetime field? If so, that solves half your problem. Datetime fields are represented in this way.

Answer 5

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);