许多记录的平均经过时间

Question

Using PHP to access mySQL.

I've got 10,000+ records, each of which has a Start-time and an End-time.

I'll want to work with these records a lot, so I'd like to understand how I can optimize queries as I write PHP functions to explore the data.

This first one, I want the average elapsed time over all records, i.e. the average of all (end minus start).

[EDIT] Apologies: the question is: can I write a single query that will return the average of all timediffs of all my records? Or do I do my averaging in PHP after getting the TIMEDIFFS back (in a loop of 10,000 iterations)?

If I can do it in the mySQL query, how do I construct it? Clearly, it will involve TIMEDIFF() and AVG(), but I'm not sure if I can do it in one query or if I need more.

This is malformed: SELECT AVG(SELECT TIMEDIFF(startdatetime,enddatetime) from myTable) from myTable

Assume this is my table:

myTable: ID startdatetime enddatetime 1 2014-05-06 12:31:00 1 2014-05-06 12:41:00 2 2014-05-06 12:51:00 1 2014-05-06 12:55:00

I want to get back the average: (41-31)+(55-51)/2 = 7

(I imagine I'll have to convert the elapsed time to seconds, then average it, then convert it back to minutes.)

Answer 1

Ah OK. Once I figured out what I was after, the searching around got a lot easier. I've been able to piece together this:

SELECT SEC_TO_TIME(AVG(TIME_TO_SEC(TIMEDIFF(enddatetime,startdatetime)))) from myTable WHERE (enddatetime IS NOT NULL AND startdatetime IS NOT NULL)