MySQL date_add()在WHERE子句中不起作用?

请注意 DATE_ADD() 函数返回日期之外的时间部分。这与使用php date("Y-m-d", strtotime('+10 hours')) ,它只返回日期部分。

SELECT DATE_ADD(NOW(), INTERVAL 10 HOUR);
+-----------------------------------+
| DATE_ADD(NOW(), INTERVAL 10 HOUR) |
+-----------------------------------+
| 2010-11-05 01:59:51               |
+-----------------------------------+
1 row in set (0.00 sec)

WHERE date = DATE(DATE_ADD(NOW(), INTERVAL 10 HOUR)) 相反:

SELECT DATE(DATE_ADD(NOW(), INTERVAL 10 HOUR));
+-----------------------------------------+
| DATE(DATE_ADD(NOW(), INTERVAL 10 HOUR)) |
+-----------------------------------------+
| 2010-11-05                              |
+-----------------------------------------+
1 row in set (0.02 sec)

测试用例:

CREATE TABLE tbl (id int, date datetime);

INSERT INTO tbl VALUES (1, '2010-11-04');
INSERT INTO tbl VALUES (2, '2010-11-05');
INSERT INTO tbl VALUES (3, '2010-11-06');
INSERT INTO tbl VALUES (4, '2010-11-07');

SELECT * FROM tbl WHERE date = DATE(DATE_ADD(NOW(), INTERVAL 10 HOUR));
+------+---------------------+
| id   | date                |
+------+---------------------+
|    2 | 2010-11-05 00:00:00 |
+------+---------------------+
1 row in set (0.00 sec)

NOW() 是时间戳,不是日期。

mysql> select now(), current_date();
+---------------------+----------------+
| now()               | current_Date() |
+---------------------+----------------+
| 2010-11-04 12:00:01 | 2010-11-04     |
+---------------------+----------------+

我想这就是你想要的:

... WHERE date = DATE_ADD(CURRENT_DATE(), INTERVAL 10 HOUR)

比较日期时间(的结果 DATE_ADD )有日期。你需要 WHERE date = DATE(DATE_ADD(NOW(), INTERVAL 10 HOUR))

WHERE date = DATE_ADD(NOW(), INTERVAL 10 HOUR)

…确实有效(假设 date 是日期时间 ), but it includes the time portion when the statement was executed -- the 将被归还。