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

Question

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?

Answer 1

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.

Answer 2

SELECT [username], AVG(TIMESTAMPDIFF(HOUR, start_date, end_date))
FROM [table]
GROUP BY [username]