Average difference between two dates, grouped by a third field?

sql

Solution

You don't specify the granularity you want for the diff. This does it in days:

select username, avg(end_date - start_date) as avg_days
from mytable
group by username

If you want the difference in seconds, use `datediff()`:

select username, avg(datediff(ss, start_date, end_date)) as avg_seconds
...

datediff can measure the diff in any time unit up to years by varying the first parameter, which can be ss, mi, hh, dd, wk, mm or yy.

Problem

So say we have 3 fields, `username, start_date, end_date` Users start and stop multiple records, eg below bob has started and stopped two records. ``` bob 1/2/13 11:00 1/2/13 13:00 jack 1/2/13 15:00 1/2/13 18:00 bob 2/2/13 14:00 1/2/13 19:00 ``` I need to know the average time taken (ie diff between start and end), in hours, for each user (ie group by user, not just for each row). I can't quite get my head around how to do the diff, average AND group by? Any help?

Original source