MySQL: Get average of time differences?

Posted by Nebs on Stack Overflow See other posts from Stack Overflow or by Nebs
Published on 2010-04-27T14:50:52Z Indexed on 2010/04/27 14:53 UTC
Read the original article Hit count: 274

Filed under:
|
|

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.

© Stack Overflow or respective owner

Related posts about mysql

Related posts about datetime