Longest interval between occourences

[soycharliente]

I have a db table with date fields to track the occurrences of events. How would I go about trying to find the longest interval between the same event occurring twice? Each event has a unique id.

 

This is returning the most recent event's date for all events. Is it possible to edit it to do what I want?

<?php
$sql = "SELECT el.id, el.loc, d.thedate
FROM `lunch_loc` el, lunch_data d
WHERE el.id = d.pid
AND d.thedate = (SELECT d2.thedate
	FROM `lunch_data` d2
	WHERE d2.pid = el.id
	ORDER BY d2.thedate DESC
	LIMIT 1)
ORDER BY d.thedate DESC";
?>

 

Alternatively, I thought that I'd have to simply grab all dates and then use PHP find out the length of time between each one. Do you think that's a better solution? I figure if MySQL can do the same thing I'd let it. (I just don't know MySQL that well.)

 

Any advice at all will help. If you don't want to supply code, I'm cool with that. Just some functions to look at are enough to help me learn.

 

 

[premiso]

You would select it by however you would determine the same event, then order it by date and pull the first date then the second date and use that to determine the time intervals.

 

You can use MySQL to determine that with it's built in date functions (google DATE MYSQL for more information on them).  Hope that helps a bit.

[sasa]

SELECT d3.* , el.loc, el.id
FROM (
SELECT d.pid, d.thedate, MIN( d2.thedate ) next_date, MIN( d2.thedate ) - d.thedate AS dif
FROM  `lunch_data` d, lunch_data d2
WHERE d2.thedate > d.thedate
AND d.pid = d2.pid
GROUP BY d.thedate
ORDER BY dif DESC , d.thedate DESC
) AS d3, lunch_loc` el
where el.id=d3.pid
GROUP BY d3.pid