Computing average time given set of start and end dates

Question

Hey.

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 much 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.

Answer 1

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

Answer 2

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

Answer 3

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.