CODEWITHSUNDEEP
mysql
cyrianox
cyrianox

Reputation: 182

How to calculate average time difference between lines?

I have a mysql table 'totolog' :

id  dt                    data
1   2013-05-01 06:01:01   hi john  
2   2013-05-01 06:04:23   hi bob  
3   2013-05-01 07:17:36   hi alex  
4   2013-05-01 14:49:41   hi all

How can I retrieve the average time difference between lines?

I suspect something like this:

SELECT UNKNOW_FUNCTION(dt) FROM totolog ORDER BY dt ASC;

Upvotes: 1

Views: 103

Answers (2)

Strawberry
Strawberry

Reputation: 33945

Given a dataset like this...

CREATE TABLE totolog
 (id  INT NOT NULL AUTO_INCREMENT PRIMARY KEY
 ,dt                    DATETIME NOT NULL
 ,data VARCHAR(20) NOT NULL
 );

 INSERT INTO totolog VALUES
 (1   ,'2012-12-01 06:01:01','hi john'),
 (2   ,'2013-01-01 06:04:23','hi bob'),
 (3   ,'2013-02-01 07:17:36','hi alex'),
 (4   ,'2013-03-28 14:49:41','hi all');

I'd imagine you'd want something like this...

SELECT FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(dt)))x FROM totolog;
+---------------------+
| x                   |
+---------------------+
| 2013-01-22 20:33:10 |
+---------------------+
1 row in set (0.00 sec)

...or this...

SELECT FROM_UNIXTIME((MAX(UNIX_TIMESTAMP(dt))+MIN(UNIX_TIMESTAMP(dt)))/2)x 
  FROM totolog;
+---------------------+
| x                   |
+---------------------+
| 2013-01-28 22:25:21 |
+---------------------+
1 row in set (0.00 sec)

Upvotes: 1

juergen d
juergen d

Reputation: 204766

The average time would be time_max - time_min / number_of_records

SELECT (max(dt) - min(dt)) / count(dt) as avg_dt
FROM totolog 
ORDER BY avg_dt ASC;

Upvotes: 1

Related Questions