Geek Answers Handbook

How do you create an average time difference between records in MySQL?

I want to create an average time difference between device connections.

Now I have this request thanks to ajreal. Now I need to create an average time between each device record.

select device, count(*) as cnt, max(time) from database.table group by device having cnt>1 order by device;

I played with TIMEDIFF, but I need the average value between all device entries, not just min and max.

The table is structured as follows:

 ID, device(string), data1(int),data2(int), time(timestamp), data3(int), data4(int)

How can I achieve my goal?

This is a table definition.

Field Type Null Key Default Extra

id int (11) NO PRI NULL auto_increment device varchar (15) NO MUL NULL
data1 decimal (5,2) YES NULL
data2 decimal (5,2) YES NULL
timestamp YES NULL
data3 decimal (3.0) YES NULL
data4 decimal (3.0) YES NULL

Thanks for looking at my problem @ Chris Henry!

+4
source share
4 answers

You need to convert the timestamp to a number in order to be able to round it. And when you have the average timestamp, convert it back to date.

 select device, count(*) as cnt, FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(time)), '%i:%s') from database.table group by device having cnt>1 order by device;

Note. If you count a negative time, your average is obvious! If you do not, then it is better to calculate it using a scripting language (php, C #, ruby โ€‹โ€‹...)

 average := avg(ts1โ€“ts2, ts2-ts3, ts3-ts4) = (ts1โ€“ts2 + ts2-ts3 + ts3-ts4)/3 = (ts4-ts1)/3

Generalization:

 average = (last entry - first entry) / (number of entries - 1)
+2
source

This will give you a time difference for each event (safely neglecting the first):

 SELECT device, TIME_TO_SEC(TIMEDIFF(time1, time0)) as diff FROM table t0 JOIN table t1 ON t0.device = t1.device AND t0.time < t1.time LEFT JOIN table t2 ON t1.device = t2.device AND t2.time < t1.time AND t2.time > t1.time WHERE t2.id IS NULL

Then take the average value for the device.

 SELECT device, AVG(diff) FROM ( put the above query here ) AS tbl GROUP BY device
+1
source 
 SELECT device , COUNT(*) AS cnt , TIME_FORMAT( TIMEDIFF(MAX(`time`), MIN(`time`)) / (COUNT(*)-1) , '%H:%i:%s:%f' ) AS avgTimeDiff FROM database.table WHERE `time` IS NOT NULL GROUP BY device HAVING COUNT(*) > 1 ORDER BY device
0
source

More articles:


All Articles