Menu
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?
425
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. Save this answer.
SELECT TO_DATE(date1, 'yyyy/mm/dd') + ((TO_DATE(date2, 'yyyy/mm/dd') - TO_DATE(date1, 'yyyy/mm/dd')) /2 ) FROM dual; This will calculate the elapsed time between date1 and date2. Then it takes half of the elapsed time and adds it to date1. This should give you the average date.
The SQL AVG function calculates the average of a series of values that you provide to it. Most of the time, this will be a particular column that you specify, so the average will be all of the values in that column. Just like the MIN and MAX functions, the AVG function is a SQL standard column.
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.
69
SELECT [username], AVG(TIMESTAMPDIFF(HOUR, start_date, end_date))
FROM [table]
GROUP BY [username]
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
