php - 获取两个连续的日期，它们之间的时间最长

Question

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

日期是从按日期升序排序的 mysql 数据库中检索的。

我需要一个 mysql 或 PHP 脚本来获取两个 CONSECUTIVE 天数相差最大的日期。

说明:脚本应该计算$a 和$b、$b 和$c、$c 和$d、$d 和$e、$e 和$a 之间的天数，然后用最大天数差异。

有没有办法使用快速的 mysql/php 代码来做到这一点，或者我应该使用以下脚本进行一些循环(在 stackoverflow 上的另一个问题上找到它)？

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

列出日期的查询:

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

Answer 1

MySQL解决方案

假设每个日期都有一个连续的 id。看吧in action .

架构

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')

查询

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

结果

|                         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";

结果

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

更新 1

关于 PHP 解决方案，如果您的日期没有按顺序排列，您可以在循环之前使用 sort($array); 对数组进行排序。