mysql - 在 mySQL 中遇到 WHERE DATEDIFF 问题

Question

我想我做错了。我想用 PHP 提取数据库中不到一天的行。我正在使用 Laravel，但这不相关。这是我所拥有的:

if ($day == 'today') {
    $date = date('Y-m-d H:i:s');
} elseif ($day == 'yesterday') {
    $date = date('Y-m-d H:i:s', strtotime('yesterday'));
} else {
    ...
}

$sql = "SELECT * FROM all_forms_main_contact WHERE DATEDIFF(timestamp, '" . $date . "') <= 1";
$mc = DB::select($sql);
return json_encode($mc);

但是，这并没有返回正确的结果。它返回超过一天的结果。

Answer 1

这实际上取决于您如何定义“不到一天”。

MySQL DATEDIFF 函数只考虑日期时间值的日期部分，并返回整数天数差。下面两个查询都会返回值 1，代表“1 天”，其中一个相差 8 小时，另一个相差 46 小时:

SELECT DATEDIFF('2013-01-15 07:00','2013-01-14 23:00') AS 8_hrs
SELECT DATEDIFF('2013-01-15 23:00','2013-01-14 01:00') AS 46_hrs

因此，您的查询完全有可能返回“早于”24 小时的行。

(作为参数提供给 DATEDIFF 函数的文字的 hh:mi:ss 时间部分被忽略，时间戳值的时间部分也被忽略。你会得到仅提供 YYYY-MM-DD 值即可得到相同的结果。)

---

另请注意，DATEDIFF 函数的谓词不可优化，即 MySQL 无法对索引进行范围扫描以满足该谓词。

为了获得最佳性能，我们通常更喜欢裸列上的等效谓词，例如

WHERE `timestamp` >= expr

这使优化器至少可以考虑对 timestamp 列上的索引使用范围扫描。

如果您要查找不到 1 天(即不到 24 小时或不到 86400 秒)的行，并且分辨率高达一秒，您可以使用如下谓词之一:

WHERE `timestamp` >= '2013-01-15 16:10' + INTERVAL -1 DAY
WHERE `timestamp` >= '2013-01-15 16:10' + INTERVAL -24 HOUR
WHERE `timestamp` >= '2013-01-15 16:10' + INTERVAL -86400 SECOND

DATE_ADD 函数是等价的:

WHERE `timestamp` >= DATE_ADD('2013-01-15 16:10',INTERVAL -1 DAY)

另请注意，没有必要在 SQL 文本中将当前日期值作为文字传递； MySQL 提供了从 MySQL 服务器返回当前日期和时间的内置函数，例如 NOW()、CURRENT_DATE() 和 SYSDATE() .

对于小于 24 小时或 48 小时的行，分别为:

WHERE `timestamp` >= DATE_ADD(NOW(),INTERVAL -1 DAY)
WHERE `timestamp` >= DATE_ADD(NOW(),INTERVAL -2 DAY)