Average time of operations stored in the database

Question

I need to calculate the average time of all the operations stored in the database. The table I store operations in looks as follows:

 creation time       | operation_type | operation_id
 2017-01-03 11:14:25 | START          | 1
 2017-01-03 11:14:26 | START          | 2
 2017-01-03 11:14:28 | END            | 2
 2017-01-03 11:14:30 | END            | 1

In this case operation 1 took 5 seconds and operation 2 took 2 seconds to finish.

How can I calculate the average of these operations in MySQL?

EDIT: It seems that operation_id doesn't need to be unique - given operation may be executed several times, so the table might look as follows:

 creation time       | operation_type | operation_id
 2017-01-03 11:14:25 | START          | 1
 2017-01-03 11:14:26 | START          | 2
 2017-01-03 11:14:28 | END            | 2
 2017-01-03 11:14:30 | END            | 1
 2017-01-03 11:15:00 | START          | 1
 2017-01-03 11:15:10 | END            | 1

What should I add in the query to properly calculate the average time of all these operations?

juergen d · Answer 1 · 2017-01-03T13:58:48.213

2

Since the END of an operation is always after the START you can use MIN and MAX

select avg(diff)
from
(
      select operation_id, 
             TIME_TO_SEC(TIMEDIFF(max(creation_time), min(creation_time))) as diff
      from your_table
      group by operation_id
) tmp

edited Jan 03 '17 at 13:58

answered Jan 03 '17 at 11:37

juergen d

201,996
37
293
362

Alright - you are right. I added `TIME_TO_SEC` to get the end result in seconds. – juergen d Jan 03 '17 at 13:59

score 2 · Accepted Answer · answered Jan 03 '17 at 12:13

2

I'm not sure that a subquery is necessary...

SELECT AVG(TIME_TO_SEC(y.creation_time)-TIME_TO_SEC(x.creation_time)) avg_diff
  FROM my_table x 
  JOIN my_table y 
    ON y.operation_id = x.operation_id 
   AND y.operation_type = 'end' 
 WHERE x.operation_type = 'start';

answered Jan 03 '17 at 12:13

Strawberry

33,750
13
40
57

Thanks, it's the best and the easiest solution! Do you know how can I modify this query to take into an account fact that operation_id is not unique? I edited the original question today and posted how the table might look like. – CorrieSparrow Jan 05 '17 at 12:25
My example relies on the fact that you have a PRIMARY KEY formed on (operation_type, operation_id) – Strawberry Jan 05 '17 at 12:34

score 1 · Answer 3 · answered Jan 03 '17 at 11:40

select avg(diff)
from
(
select a1.operation_id, timediff(a2.operation_time, a1.operation_time) as diff
from oper a1 -- No table name provided, went with 'oper' because it made sense in my head
inner join oper a2
  on a1.operation_id = a2.operation_id
where a1.operation_type = 'START'
and a2.operation_type = 'END'
)

Average time of operations stored in the database

3 Answers3

Linked