1

This related to my other question.

I have this table

CREATE OR REPLACE TABLE hits (ip bigint, page VARCHAR(256), agent VARCHAR(1000), 
                              date datetime)

and I want to calculate average time between googlebot visit for every page.

... WHERE agent like '%Googlebot%' group by page order by date

Something like 

select datediff('2010-09-18 04:20:47', '2010-09-16 05:23:04')

but for every date in table
If there is no mysql way, how can I do this in php?

Community
  • 1
  • 1
jcubic
  • 61,973
  • 54
  • 229
  • 402

1 Answers1

3
SELECT page, TIMESTAMPDIFF(SECOND, MIN(date), MAX(date)) / (COUNT(*)-1) FROM hits 
WHERE agent like '%Googlebot%' GROUP BY page;

TIMESTAMPDIFF(SECOND, a, b) returns the difference in seconds between the date expressions a and b. For each page, the query finds the date of the first and last visit and the total count of visits, and calculates the arithmetic average.

Simon
  • 12,018
  • 4
  • 34
  • 39