mysql 时间平均值_MySQL：获取时间差异的平均值？

I have a table called Sessions with two datetime columns: start and end.

For each day (YYYY-MM-DD) there can be many different start and end times (HH:ii:ss). I need to find a daily average of all the differences between these start and end times.

An example of a few rows would be:

start: 2010-04-10 12:30:00 end: 2010-04-10 12:30:50

start: 2010-04-10 13:20:00 end: 2010-04-10 13:21:00

start: 2010-04-10 14:10:00 end: 2010-04-10 14:15:00

start: 2010-04-10 15:45:00 end: 2010-04-10 15:45:05

start: 2010-05-10 09:12:00 end: 2010-05-10 09:13:12

...

The time differences (in seconds) for 2010-04-10 would be:

50

60

300

5

The average for 2010-04-10 would be 103.75 seconds. I would like my query to return something like:

day: 2010-04-10 ave: 103.75

day: 2010-05-10 ave: 72

...

I can get the time difference grouped by start date but I'm not sure how to get the average. I tried using the AVG function but I think it only works directly on column values (rather than the result of another aggregate function).

This is what I have:

SELECT

TIME_TO_SEC(TIMEDIFF(end,start)) AS timediff

FROM

Sessions

GROUP BY

DATE(start)

Is there a way to get the average of timediff for each start date group? I'm new to aggregate functions so maybe I'm misunderstanding something. If you know of an alternate solution please share.

I could always do it ad hoc and compute the average manually in PHP but I'm wondering if there's a way to do it in MySQL so I can avoid running a bunch of loops.

Thanks.

解决方案SELECT DATE(start) AS startdate, AVG(TIME_TO_SEC(TIMEDIFF(end,start))) AS timediff

FROM Sessions

GROUP BY

startdate