Get the two consecutive dates with the greatest period between them

Question

$a = 1950-05-01
$b = 1965-08-10
$c = 1990-12-30
$d = 1990-12-29
$e = 2012-09-03

Dates are retrieved from a mysql database ordered by date ascending.

I need a mysql or PHP script to get the two CONSECUTIVE dates with the maximum days difference.

Explaination: Script should calculate the number of days between $a and $b, $b and $c, $c and $d, $d and $e, $e and $a, then output the two dates with the maximum days difference.

Is there a way to do this with a fast mysql/php code or should I make some loops with the following script (found it on another question here on stackoverflow)?

$now = time(); // or your date as well
$your_date = strtotime("2010-01-01");
$datediff = $now - $your_date;
echo floor($datediff/(60*60*24));

Query that lists dates:

SELECT date AS count FROM table WHERE column1 = 'YES' AND data BETWEEN 1950-01-01 AND 2012-09-04

Answer 1

MySQL Solution

Assuming that each date has a sequential id. See it in action.

Schema

CREATE TABLE tbl (
  id tinyint,
  dt date);

INSERT INTO tbl VALUES 
(1, '1950-05-01'),
(2, '1965-08-10'),
(3, '1990-12-30'),
(4, '1990-12-29'),
(5, '2012-09-03')

Query

SELECT a.dt AS date1, 
    (SELECT dt FROM tbl WHERE id = a.id - 1) AS date2,
    DATEDIFF(a.dt, b.dt) AS diff
FROM tbl a
LEFT JOIN tbl b ON b.id = a.id -1
GROUP BY a.id
ORDER BY diff DESC
LIMIT 1

Result

|                         DATE1 |                           DATE2 | DIFF |
--------------------------------------------------------------------------
| August, 10 1965 00:00:00-0700 | December, 30 1990 00:00:00-0800 | 9273 |

PHP Solution

$array = array('1950-05-01', '1965-08-10', '1990-12-30', '1990-12-29', '2012-09-03');

$maxDiff = 0;
$maxStart = NULL;
$maxEnd = NULL;

for($i = 1; $i <= count($array); $i++) {
    if(isset($array[$i])) {
        $diff = (strtotime($array[$i]) - strtotime($array[$i-1])) / (60*60*24);

        if($diff > $maxDiff) {
            $maxDiff = $diff;
            $maxStart = $array[$i-1];
            $maxEnd = $array[$i];
        }
    }
}

echo "The maximum days difference is between $maxStart and $maxEnd, with a difference of $maxDiff days";

Result

The maximum days difference is between 1965-08-10 and 1990-12-30, with a difference of 9273.0416666667 days

Update 1

With regards to the PHP solution, if your dates are not in order, you can sort the array before the loop using sort($array);.