2

I am writing a query for getting average value of event duration for given period of time. We might consider that we do have table with 2 columns:

start               | end
-----------------------------------------
2019-10-14 18:16:08 | null
2019-10-14 19:39:47 | 2019-10-14 23:28:17
2019-10-16 03:29:33 | 2019-10-16 03:31:08
2019-10-16 21:43:34 | 2019-10-17 02:07:31

The wanted behaviour would be to take avg. time for each day. The first row should be ignored, cuz the task is not finished - it might be still going / infinite. For second row we would get avg. time of

(2019-10-16 03:31:0 - 2019-10-16 03:29:33) + (2019-10-16 21:43:34 - 2019-10-16 23:59:59) / 2 -- 2 events only

And the for 17th day we would get 2:07:31. So far I've got this query:

SELECT 
    DATE_FORMAT(start, '%Y-%m-%d') AS time_label,
    AVG(TIME_TO_SEC(TIMEDIFF(end, start))) AS value
FROM
    time
GROUP BY start

which is not working as expected. I might add WHERE clause but it won't work. I've tested it on much bigger table.

1
  • 1
    You need to filter out ongoing events and you need to group by date
    – Lennart
    May 2 '20 at 22:14
3

You have it almost right, by grouping by start it would only group the same date and time, but when you group by time_label, it takes the individual days with out the time

SELECT 
    DATE_FORMAT(start, '%Y-%m-%d') AS time_label,
    AVG(TIME_TO_SEC(TIMEDIFF(end, start))) AS value
FROM
    timetable
GROUP BY time_label

Result

| time_label | value |
| ---------- | ----- |
| 2019-10-14 | 13710 |
| 2019-10-16 | 7966  |

View on DB Fiddle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.