sql - 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.