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);.

Answer 2

You can use this single-statement solution:

SELECT   a.date date1,
         b.date date2,
         DATEDIFF(b.date, a.date) ddiff
FROM     (
         SELECT     @a_rn:=@a_rn+1 ascrank,
                    date
         FROM       tbl
         CROSS JOIN (SELECT @a_rn:=0) var_init
         WHERE      date BETWEEN '1950-05-01' AND '2012-09-04'
         ORDER BY   date
         ) a
JOIN     (
         SELECT     @b_rn:=@b_rn+1 ascrank,
                    date
         FROM       tbl
         CROSS JOIN (SELECT @b_rn:=-1) var_init
         WHERE      date BETWEEN '1950-05-01' AND '2012-09-04'
         ORDER BY   date
         ) b ON a.ascrank = b.ascrank
ORDER BY ddiff DESC
LIMIT    1

---

Query Breakdown

---

Given this example data-set:

CREATE TABLE tbl (
  date DATE
);

INSERT INTO tbl VALUES
('1950-05-01'),
('1965-08-10'),
('1990-12-30'),
('1990-12-29'),
('2012-09-03');

We want to find the biggest difference between two consecutive dates (meaning, given the dates ordered in ascending order, find the maximum day difference of the dates and their immediate prior dates).

We would expect to output:

+-------------+------------+--------+
| date1       | date2      | ddiff  |
+-------------+------------+--------+
| 1965-08-10  | 1990-12-29 | 9272   |
+-------------+------------+--------+

Because the biggest consecutive date difference is between 1965-08-10 and 1990-12-29.

---

Step 1:

The first thing we want to do in order to get the previous and next dates beside each other (to facilitate the DATEDIFF function) is to attach a rank number to each date based on the ascending order of the dates.

Because the order of the dates can't rely upon anything but themselves (not an auto-incrementing ID or rank field, etc.) we must manually calculate the rank ourselves.

We do this by using MySQL variables. Other solutions that use variables require that you execute three or more separate statements. My technique of initializing the variables right in the query itself (via CROSS JOIN) keeps it contained in a single statement.

SELECT     @a_rn:=@a_rn+1 ascrank,
           date
FROM       tbl
CROSS JOIN (SELECT @a_rn:=0) var_init
WHERE      date BETWEEN '1950-05-01' AND '2012-09-04'
ORDER BY   date 

Renders:

+----------+------------+
| ascrank  | date       |
+----------+------------+
| 1        | 1950-05-01 |
| 2        | 1965-08-10 |
| 3        | 1990-12-29 |
| 4        | 1990-12-30 |
| 5        | 2012-09-03 |
+----------+------------+

SQLFiddle Demo

Note the WHERE condition that the dates have to be in between two specified dates. This is where you would insert your start/end date parameters from your script.

---

Step 2:

Now that we have ranked each date, we now need to perform a shifted inner join of the result onto itself based on the ascrank field so that we get the consecutive dates beside each other. We do this by wrapping the result in a subselect.

Since we need to self-join a derived result, we must duplicate the step above only with a slightly adjusted parameter:

SELECT   *
FROM     (
         SELECT     @a_rn:=@a_rn+1 ascrank,
                    date
         FROM       tbl
         CROSS JOIN (SELECT @a_rn:=0) var_init
         WHERE      date BETWEEN '1950-05-01' AND '2012-09-04'
         ORDER BY   date
         ) a
JOIN     (
         SELECT     @b_rn:=@b_rn+1 ascrank,
                    date
         FROM       tbl
         CROSS JOIN (SELECT @b_rn:=-1) var_init
         WHERE      date BETWEEN '1950-05-01' AND '2012-09-04'
         ORDER BY   date
         ) b ON a.ascrank = b.ascrank

Renders:

+----------+-------------+----------+------------+
| ascrank  | date        | ascrank  | date       | 
+----------+-------------+----------+------------+
| 1        | 1950-05-01  | 1        | 1965-08-10 | 
| 2        | 1965-08-10  | 2        | 1990-12-29 | 
| 3        | 1990-12-29  | 3        | 1990-12-30 | 
| 4        | 1990-12-30  | 4        | 2012-09-03 | 
+----------+-------------+----------+------------+

SQLFiddle Demo

The "slightly adjusted parameter" is just that the ascrank variable (@b_rn) in the second subselect starts from -1 instead of 0. That way, the join condition of a.ascrank = b.ascrank joins the next date in the ascending order. We could have also kept both variables initialized at 0, but joined on the condition of a.ascrank = b.ascrank-1, which would have rendered the same result.

But wait, what happened to the date with the ascrank of 5? Since that is the last date in the order, there would be no dates after it to take the difference from, so it doesn't need to appear in the left side of the result, it only needs to be compared with its immediate prior date.

---

Step 3:

Now that we have the consecutive dates beside each other, we can take the date difference (via DATEDIFF()) between the two:

SELECT   a.date date1,
         b.date date2,
         DATEDIFF(b.date, a.date) ddiff
FROM     (
         SELECT     @a_rn:=@a_rn+1 ascrank,
                    date
         FROM       tbl
         CROSS JOIN (SELECT @a_rn:=0) var_init
         WHERE      date BETWEEN '1950-05-01' AND '2012-09-04'
         ORDER BY   date
         ) a
JOIN     (
         SELECT     @b_rn:=@b_rn+1 ascrank,
                    date
         FROM       tbl
         CROSS JOIN (SELECT @b_rn:=-1) var_init
         WHERE      date BETWEEN '1950-05-01' AND '2012-09-04'
         ORDER BY   date
         ) b ON a.ascrank = b.ascrank

Renders:

+-------------+------------+--------+
| date1       | date2      | ddiff  |
+-------------+------------+--------+
| 1950-05-01  | 1965-08-10 | 5580   |
| 1965-08-10  | 1990-12-29 | 9272   |
| 1990-12-29  | 1990-12-30 | 1      |
| 1990-12-30  | 2012-09-03 | 7918   |
+-------------+------------+--------+

SQLFiddle Demo

---

Step 4:

Now it's a simple matter of selecting the maximum ddiff value. We do this by using an ORDER BY / LIMIT 1 technique on the ddiff field:

SELECT   a.date date1,
         b.date date2,
         DATEDIFF(b.date, a.date) ddiff
FROM     (
         SELECT     @a_rn:=@a_rn+1 ascrank,
                    date
         FROM       tbl
         CROSS JOIN (SELECT @a_rn:=0) var_init
         WHERE      date BETWEEN '1950-05-01' AND '2012-09-04'
         ORDER BY   date
         ) a
JOIN     (
         SELECT     @b_rn:=@b_rn+1 ascrank,
                    date
         FROM       tbl
         CROSS JOIN (SELECT @b_rn:=-1) var_init
         WHERE      date BETWEEN '1950-05-01' AND '2012-09-04'
         ORDER BY   date
         ) b ON a.ascrank = b.ascrank
ORDER BY ddiff DESC
LIMIT    1

Renders:

+-------------+------------+--------+
| date1       | date2      | ddiff  |
+-------------+------------+--------+
| 1965-08-10  | 1990-12-29 | 9272   |
+-------------+------------+--------+

SQLFiddle Demo of Final Result

And we have arrived at our final result.

Answer 3

I am using the table scheme of njk - and checked it on my mysql db.

SCHEME

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.id, b.id, ABS(DATEDIFF(a.dt, b.dt)) AS ddiff
 FROM tbl AS a
 JOIN tbl AS b ON (a.id = (b.id + 1)) OR (a.id = (SELECT id FROM tbl ORDER BY id ASC LIMIT 1) AND b.id = (SELECT id FROM tbl ORDER BY id DESC LIMIT 1))
 ORDER BY ddiff DESC
 LIMIT 1

I am joining all consecutive rows (a.id = (b.id + 1)) and the first row with the last one like this: (a.id = (SELECT id FROM tbl ORDER BY id ASC LIMIT 1) AND b.id = (SELECT id FROM tbl ORDER BY id DESC LIMIT 1)) which looks strange but works very fine. In case you have only the 5 rows you mentioned this would be

 SELECT a.id, b.id, ABS(DATEDIFF(a.dt, b.dt)) AS ddiff
  FROM tbl AS a
  JOIN tbl AS b ON (a.id = (b.id + 1)) OR (a.id = 1 AND b.id = 5)
  ORDER BY ddiff DESC
  LIMIT 1

EDIT: The result is 1=$a and 5=$e

Answer 4

Try this query -

SELECT
  t1.dt,
  @dt_next := (SELECT dt FROM tbl WHERE dt > t1.dt ORDER BY dt LIMIT 1) dt_next,
  DATEDIFF(@dt_next, t1.dt) max_diff
FROM tbl t1
ORDER BY max_diff DESC LIMIT 1;

+------------+------------+----------+
| dt         | dt_next    | max_diff |
+------------+------------+----------+
| 1965-08-10 | 1990-12-29 |     9272 |
+------------+------------+----------+

Answer 5

Just example:

mysql> SELECT MIN(version) AS version FROM schema_migrations UNION SELECT MAX(version) FROM schema_migrations;
+----------------+
| version        |
+----------------+
| 20120828071352 |
| 20120830100526 |
+----------------+
2 rows in set (0.00 sec)

Answer 6

if the dates are on a table you can do something like (this is not the T-SQL, its just an algorithm, to get the previous_date you would need to rn another select top 1 on the same table with an aclias X for example where X.date<=date)

select date, datediff(date, previous_date)

and order by the second column desc, so the first row would be the date you want

Answer 7

Start with a subquery that creates a result set that has the dates in ascending order and an INT field (dateOrder) that starts at 1 and increments by 1.

SET @a := 0;
SELECT date, (@a:=@a+1) AS dateOrder FROM dateTable ORDER BY date

Now we can get consecutive dates by joining this result set to another copy of itself with a.dateOrder = b.dateOrder -1. In that result set, each row contains a pair of consecutive dates from the original table, and it is easy to calculate the difference and sort the result set to find the biggest difference.

SET @a := 0; SET @b := 0;
SELECT a.date as firstDate, b.date as secondDate, 
  datediff(b.date, a.date) AS difference FROM (
    SELECT date, (@a:=@a+1) AS dateOrder FROM dateTable ORDER BY date ) a JOIN (
    SELECT date, (@b:=@b+1) AS dateOrder FROM dateTable ORDER BY date ) b 
  ON a.dateOrder = b.dateOrder - 1
ORDER BY difference desc;

You can put a 'limit 1' clause at the end of the query to only get the first row, which has the biggest value of 'difference'. Note that you have to use two different variables to generate date order for the two subqueries.

Answer 8

Your query that returns date values is non-deterministic... absent an ORDER BY clause in your query, there is NO GUARANTEE that the rows will be returned in any particular order.

In MySQL, a query can return the result set you specified. Here is one approach:

SELECT ABS(DATEDIFF(d.mydate,@prev_date))  AS days_diff
     , DATE_ADD(@prev_date,INTERVAL 0 DAY) AS date1
     , @prev_date := d.mydate              AS date2
  FROM ( SELECT @prev_date := NULL) i
  JOIN ( SELECT d1.*
           FROM (            -- query to return rows in a specific order
                             SELECT mydate       
                               FROM mytable3 
                              WHERE 1
                              ORDER BY foo
                ) d1
          UNION ALL 
         SELECT d2.*
           FROM (            -- query to return rows in a specific order (again)
                             SELECT mydate
                               FROM mytable3 
                              WHERE 1
                              ORDER BY foo
                   LIMIT 1
                 ) d2
       ) d
 ORDER BY days_diff DESC

NOTES:

The ABS() function is needed only if you want to consider the number of days between the dates, irrespective of whether the first date is before or after the second date, since the DATEDIFF function can return a negative value.

The DATE_ADD( ,INTERVAL 0 DAY) function around the @prev_date user variable is just there to cast the return value to datatype DATE. A `STR_TO_DATE( ,'%Y-%m-%d') function would work as well. (The difference is that the DATE_ADD function will work with DATE, DATETIME and TIMESTAMP columns without having to specify a format string to include hours, minutes, seconds.)

The inline views aliased as d1 and d2 contain the query that returns the list of dates in the SPECIFIED order you want the rows (dates) compared in. You need the order of those rows to be deterministic if you want to guarantee a consistent result from the query.

The query in the inline view aliased as d2 is identical to the query in d1, except for the addition of the LIMIT 1 clause. Because you specified that you wanted to compare $e to $a, we "tack on" that first row from the query to the end so we can compare that first row with the last row from the query.

The date1 column in the result set is not a DATE datatype, but it can easily be cast to a DATE

If you want other columns returned from the two rows, along with the date value, that can be easily handled using the same approach. The queries in d1 and d2 just need to return the additional columns:

SELECT ABS(DATEDIFF(d.mydate,@prev_date)) AS days_diff
     , @prev_foo                          AS foo1
     , @prev_date                         AS date1
     , @prev_foo  := d.foo                AS foo2
     , @prev_date := d.mydate             AS date2
  FROM ( SELECT @prev_date := NULL, @prev_foo := NULL) i
  JOIN ( SELECT d1.*
           FROM (            -- query to return rows in a specific order
                             SELECT mydate, foo
                               FROM mytable3 
                              WHERE 1
                              ORDER BY foo
                ) d1
          UNION ALL 
         SELECT d2.*
           FROM (            -- query to return rows in a specific order (again)
                             SELECT mydate, foo
                               FROM mytable3 
                              WHERE 1
                              ORDER BY foo
                   LIMIT 1
                 ) d2
       ) d
 ORDER BY days_diff DESC
 LIMIT 1

---

To setup test case:

CREATE TABLE `mytable3` (`foo` varchar(1), `mydate` date);

INSERT INTO mytable3 VALUES 
('a','1950-05-01'),
('b','1965-08-10'),
('c','1990-12-30'),
('d','1990-12-29'),
('e','2012-09-03');

Answer 9

Here's PHP solution

$dates  = array('1970-05-01', '1975-08-10', '1990-12-30', '1990-12-29', '2012-09-03');
$sorted = array();

foreach($dates as $i => $date) {
    $date2 = isset($dates[$i+1]) ? $dates[$i+1] : $dates[0];
    $diff  = (strtotime($date2) - strtotime($date))/(60 * 60 * 24);

    $sorted[abs($diff)] = array('start' => $date, 'end' => $date2);
}

ksort($sorted);

$result = end($sorted);

Answer 10

I would use some simple PHP since it's quick and neat:

function get_the_two_consecutive_dates_with_the_maximum_days_difference($dates) {
    foreach ($dates as $i => $date) {

        $previousDate = $dates[$i - 1];
        if (!$previousDate) continue;

        $diff = strtotime($date) - strtotime($previousDate);
        if ($maxDiff < $diff) {
            $maxDiff = $diff;
            $dateA = $previousDate;
            $dateB = $date;
        }

    }
    return array($dateA, $dateB, $maxDiff);

}

// Usage
$arr = Array ( '2012-01-01', '2012-02-01', '2012-03-01', '2012-04-12', 
               '2012-05-10', '2012-08-05', '2012-09-01', '2012-09-04' );

var_dump(get_the_two_consecutive_dates_with_the_maximum_days_difference($arr));

Answer 11

I have gone for a solution using PHP's DateTime class. The reason for this is that strtotime() does not have a way of specifying the format of the dates passed to it. In my mind this creates an ambiguity over what will be returned, so I have stopped using it in favour of DateTime.

As the example dates you gave are not in the correct order, I have assumed that they need to be sorted first. The following function achieves this:-

/**
 * Sorts an array of dates in given format into date order, oldest first
 * @param array $dates
 * @param type $format Optional format of dates.
 * 
 * @return array with dates in correct order.
 */
function sortArrayOfDates(array $dates, $format = 'Y-m-d')
{
    $result = array();
    foreach($dates as $date){
        $timeStamp = DateTime::createFromFormat($format, $date)->getTimestamp();
        $result[$timeStamp] = $date;
    }
    sort($result);
    return $result;
}

Now we can write a function to do the job:-

/**
 * Returns the longest gap between sets of dates
 * 
 * @param array $dates
 * @param string Optional. Format of dates.
 * 
 * @return array Containing the two dates with the longest interval and the length of the interval in days.
 */
private function longestGapBetweenDates(array $dates, $format = 'Y-m-d')
{
    $sortedDates = sortArrayOfDates($dates);
    $maxDiff = 0;
    $result = array();
    for($i = 0; $i < count($dates) - 1; $i++){
        $firstDate = DateTime::createFromFormat($format, $sortedDates[$i]);
        $secondDate = DateTime::createFromFormat($format, $sortedDates[$i + 1]);
        $diff = $secondDate->getTimestamp() - $firstDate->getTimestamp();
        if($diff > $maxDiff){
            $maxDiff = $diff;
            $result = array($firstDate->format($format), $secondDate->format($format), $firstDate->diff($secondDate)->days);
        }
    }
    return $result;
}

With your example list:-

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

var_dump(longestGapBetweenDates(array($a, $b, $c, $d, $e)));

Output:-

array
  0 => string '1965-08-10' (length=10)
  1 => string '1990-12-29' (length=10)
  2 => int 9272

As a bonus my function gives you the number of days between the two dates too.

Answer 12

    Select t1.date as 'Date1', t2.date AS 'Date2', DATEDIFF(t2, t1) as 'DateDiff'
From    YourTable t1
    Left outer join YourTable t2
        ON     t1.Id <= t2.Id 
            OR (t1.Id = (Select Max(Id) From YourTable) AND t2.Id=(SELECT Min(Id) From YourTable) )
    Left outer join YourTable t3
        ON t1.Id < t3.Id AND t3.Id < t2.Id
    WHERE t3.Id IS NULL
    ORDER BY 'DateDiff' DESC
    Limit 1,1

Answer 13

For this to work around, use an array with this function:

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

function get_max_difference_dates($dates=array()){
    if(!count($dates)){
        return false;
    }
    $max_dates_diff = 0;
    $max_dates_diff_index = 0;
    for($i=0;$i<count($dates)-1;$i++){
        $temp_diff = strtotime($dates[$i+1]) - strtotime($dates[$i]);
        if($temp_diff>$max_dates_diff){
            $max_dates_diff = $temp_diff;
            $max_dates_diff_index = $i;
        }
    }
    return $max_dates_diff_index;
}

var_dump(get_max_difference_dates($date_array));

the answer to the above as per my compilation is "1".

after the index you ca fetch the dates by the returned index and by adding one to it.

$indx = get_max_difference_dates($date_array);
$date1 = $date_array[$indx];
$date2 = $date_array[$indx+1];