$a = 1950-05-01
$b = 1965-08-10
$c = 1990-12-30
$d = 1990-12-29
$e = 2012-09-03
是从按日期升序排序的 mysql 数据库中检索的。
我需要一个 mysql 或 PHP 脚本来获取具有最大天差的两个连续日期。
说明：脚本应计算$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
MySQL 解决方案
假设每个日期都有一个连续的id。看到它的实际效果。
图式
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
结果
| 日期1 | 日期2 |差异 |--------------------------------------------------------------------------|八月， 10 1965 00：00：00-0700 |十二月， 30 1990 00：00：00-0800 |9273 |
PHP解决方案
$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";
结果
最大天数差在 1965-08-10 和 1990-12-30 之间，相差 9273.04166666667 天
更新 1
关于 PHP 解决方案，如果您的日期不按顺序排列，您可以使用 sort($array); 在循环之前对数组进行排序。
您可以使用以下单语句解决方案：
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
查询细分
给定此示例数据集：
CREATE TABLE tbl (
date DATE
);
INSERT INTO tbl VALUES
('1950-05-01'),
('1965-08-10'),
('1990-12-30'),
('1990-12-29'),
('2012-09-03');
我们希望找到两个连续日期之间的最大差异(这意味着，给定按升序排序的日期，找到日期与其前一个日期的最大日差(。
我们希望输出：
+-------------+------------+--------+
| date1 | date2 | ddiff |
+-------------+------------+--------+
| 1965-08-10 | 1990-12-29 | 9272 |
+-------------+------------+--------+
因为连续最大的日期差异是在1965-08-10和1990-12-29之间。
第 1 步：
为了将上一个和下一个日期并排放置(以方便DATEDIFF功能(，我们要做的第一件事是根据日期的升序为每个日期附加一个排名号。
因为日期的顺序不能依赖于任何东西，只能依赖于它们自己(不是自动递增的ID或排名字段等(，我们必须自己手动计算排名。
我们通过使用 MySQL 变量来做到这一点。使用变量的其他解决方案要求您执行三个或更多单独的语句。我在查询本身(通过CROSS JOIN(中初始化变量的技术将其包含在单个语句中。
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
呈现：
+----------+------------+
| ascrank | date |
+----------+------------+
| 1 | 1950-05-01 |
| 2 | 1965-08-10 |
| 3 | 1990-12-29 |
| 4 | 1990-12-30 |
| 5 | 2012-09-03 |
+----------+------------+
SQLFiddle 演示
请注意WHERE条件，即日期必须位于两个指定日期之间。您可以在此处插入脚本中的开始/结束日期参数。
第 2 步：
现在我们已经对每个日期进行了排名，我们现在需要根据ascrank字段将结果的移位内部联接到自身上，以便我们得到彼此相邻的连续日期。我们通过将结果包装在子选择中来做到这一点。
由于我们需要自连接派生结果，因此我们必须仅使用稍微调整的参数复制上述步骤：
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
呈现：
+----------+-------------+----------+------------+
| 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 演示
"稍微调整的参数"只是第二个子选择中的 ascrank 变量 ( @b_rn ( 从 -1 而不是 0 开始。这样，a.ascrank = b.ascrank 的连接条件将按升序连接下一个日期。我们也可以保持两个变量在0初始化，但在a.ascrank = b.ascrank-1的条件下连接，这将得到相同的结果。
但是等等，5的曲柄的日期发生了什么？由于这是订单中的最后一个日期，因此它之后没有日期可以取差额，因此它不需要出现在结果的左侧，只需要将其与之前的日期进行比较。
第 3 步：
现在我们有连续的日期彼此相邻，我们可以取两者之间的日期差(通过DATEDIFF()(：
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
呈现：
+-------------+------------+--------+
| 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 演示
第 4 步：
现在，只需选择最大ddiff值即可。我们通过在ddiff字段上使用ORDER BY / LIMIT 1技术来做到这一点：
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
呈现：
+-------------+------------+--------+
| date1 | date2 | ddiff |
+-------------+------------+--------+
| 1965-08-10 | 1990-12-29 | 9272 |
+-------------+------------+--------+
最终结果的 SQLFiddle 演示
我们已经得出了最终结果。
我正在使用 njk 的表方案 - 并在我的 mysql db 上检查了它。
方案
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.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
我(a.id = (b.id + 1))和第一行将所有连续的行与最后一行连接起来，如下所示： (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))看起来很奇怪，但效果很好。如果您只有您提到的 5 行，这将是
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
编辑：结果是1 = $a和5 = $e
试试这个查询 -
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 |
+------------+------------+----------+
举个例子：
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)
如果日期在表上，您可以执行以下操作(这不是 T-SQL，它只是一个算法，要获得previous_date，您需要在同一表上使用 aclias X 重新选择前 1 名，例如 X.date<=date(
select date, datediff(date, previous_date)
并按第二列 desc 排序，因此第一行将是您想要的日期
从一个子查询开始，该子查询创建一个结果集，该结果集的日期按升序排列，以及一个从 1 开始并递增 1 的 INT 字段 (dateOrder(。
SET @a := 0;
SELECT date, (@a:=@a+1) AS dateOrder FROM dateTable ORDER BY date
现在，我们可以通过将这个结果集连接到它的另一个副本来获得连续的日期，a.dateOrder = b.dateOrder -1。在该结果集中，每一行都包含原始表中的一对连续日期，并且很容易计算差异并对结果集进行排序以找到最大的差异。
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;
您可以在查询末尾放置"limit 1"子句，以仅获取第一行，该行具有最大的"差异"值。请注意，您必须使用两个不同的变量来为两个子查询生成日期顺序。
返回日期值的查询是不确定的...如果查询中没有 ORDER BY 子句，则无法保证行将以任何特定顺序返回。
在 MySQL 中，查询可以返回您指定的结果集。这里有一种方法：
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
笔记：
仅当您要考虑日期之间的天数(无论第一个日期是在第二个日期之前还是之后(时，才需要 ABS() 函数，因为 DATEDIFF 函数可以返回负值。
围绕 @prev_date 用户变量的 DATE_ADD( ,INTERVAL 0 DAY) 函数只是为了将返回值强制转换为数据类型 DATE。'STR_TO_DATE( ，'%Y-%m-%d'( 函数也可以工作。(不同之处在于，DATE_ADD函数将处理 DATE、DATETIME 和 TIMESTAMP 列，而无需指定包含小时、分钟、秒的格式字符串。
别名为 d1 和 d2 的内联视图包含按您希望比较行(日期(的指定顺序返回日期列表的查询。 如果要保证查询的结果一致，则需要这些行的顺序具有确定性。
内联视图中别名为 d2 的查询与 d1 中的查询相同，只是添加了 LIMIT 1 子句。 由于您指定要$e与$a进行比较，因此我们"附加"从查询到末尾的第一行，以便我们可以将第一行与查询中的最后一行进行比较。
结果集中的date1列不是 DATE 数据类型，但可以轻松地将其转换为 DATE
如果您希望从两行返回其他列以及日期值，则可以使用相同的方法轻松处理。d1 和 d2 中的查询只需要返回其他列：
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
设置测试用例：
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');
这是PHP解决方案
$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);
我会使用一些简单的PHP，因为它既快速又整洁：
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));
我已经使用PHP的DateTime类寻求解决方案。原因是 strtotime(( 没有办法指定传递给它的日期的格式。在我看来，这会对将返回的内容产生歧义，因此我已停止使用它而使用日期时间。
由于您给出的示例日期顺序不正确，因此我假设它们需要首先排序。以下函数实现此目的：-
/**
* 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;
}
现在我们可以编写一个函数来完成这项工作：-
/**
* 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;
}
使用您的示例列表：-
$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)));
输出：-
array
0 => string '1965-08-10' (length=10)
1 => string '1990-12-29' (length=10)
2 => int 9272
作为奖励，我的函数也为您提供两个日期之间的天数。
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
要解决此问题，请使用具有此函数的数组：
<?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));
根据我的汇编，上述问题的答案是 "1" .
在索引之后，您可以通过返回的索引并向其添加一个来获取日期。
$indx = get_max_difference_dates($date_array);
$date1 = $date_array[$indx];
$date2 = $date_array[$indx+1];