Calculate the average difference between two date columns for multiple rows

Question

I want to calculate the average difference between multiple dates: sent_date & view_date.

My table structure look like:

CREATE TABLE `mails` (
  `m_id` int(8) NOT NULL AUTO_INCREMENT,
  `sent_date` date NOT NULL DEFAULT '0000-00-00',
  `view_date` date NOT NULL DEFAULT '0000-00-00',
  PRIMARY KEY (`l_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;

Example data:

sent_date: 2013-06-01 view_date: 2013-06-02 difference: 2 days

sent_date: 2013-06-01 view_date: 2013-06-05 difference: 4 days

Average: 3 days

[What have you tried?](http://mattgemmell.com/2008/12/08/what-have-you-tried/) See [about Stack Overflow](http://stackoverflow.com/about). — John Conde, Jun 14 '13 at 13:37
I tried some things with DATEDIFF and SUM, but without any results — Citizen SP, Jun 14 '13 at 13:38
Possible Duplicate of: http://stackoverflow.com/questions/1021947/how-can-i-make-an-average-of-dates-in-mysql — Menelaos, Jun 14 '13 at 13:41

score 6 · Accepted Answer · answered Jun 14 '13 at 13:38

6

Use DATEDIFF() and AVG()

select avg(datediff(view_date, sent_date))
from mails

answered Jun 14 '13 at 13:38

juergen d

201,996
37
293
362

Calculate the average difference between two date columns for multiple rows

1 Answers1