有麻烦的地方DATEDIFF在我的SQL

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函数只考虑datetime值的日期部分，并返回一个差天数的整数。下面的两个查询都将返回值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函数上的谓词是不可sargable的，也就是说，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)

Answer 2

这个查询选择小于-24小时的行。
例如，如果是04:00 AM，则返回所有行>=昨天04:00 AM:

SELECT * FROM `tbl` WHERE `datetime` >= NOW() - INTERVAL 1 DAY

小于-48小时也是如此。
例如，如果是04:00 AM，则返回所有行>=前天04:00 AM:

SELECT * FROM `tbl` WHERE `datetime` >= NOW() - INTERVAL 2 DAY

这个查询选择今天的行。
例如，如果它是04:00 AM，那么返回所有行>=今天00:00 AM:

SELECT * FROM `tbl` WHERE `datetime` >= CURDATE()

同样，这个查询只选择昨天的行。
例如，如果是04:00 AM，则返回从昨天00:00 AM到(但不包括)今天00:00 AM之间的所有行:

SELECT * FROM `tbl` WHERE `datetime` >= CURDATE() - INTERVAL 1 DAY AND `datetime` < CURDATE()

_{我使用上述语法进行日期查询，我相信这比日期add/sub函数更具可读性。}

Answer 3

为什么不这样做呢:

SELECT ... WHERE timestamp BETWEEN DATE_SUB(NOW(), INTERVAL 1 DAY) AND NOW()

Answer 4

您通常可以这样简单地编写代码:

...
where timestamp > subdate(now(), 1)

注意subdate()的方便版本，它允许将天数指定为一个简单的整数(即默认的间隔类型是DAY)。此外，如果第一个参数是日期，则subdate返回日期时间。

Answer 5

虽然DATEDIFF忽略时间组件(参见DATEDIFF - MariaDB知识)，但TIMESTAMPDIFF不会。这样就可以了:

SELECT ... WHERE TIMESTAMPDIFF(DAY, NOW(), `timestamp`) = '0'

例如，如果NOW() = '2020-01-02 00:00:01'并且timestamp为'2020-01-01 01:00:00'，上述代码将准确地返回0天，但DATEDIFF将错误地返回1天(因为它忽略了时间组件)。