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

发布于 2024-09-30 07:50:03 字数 231 浏览 1 评论 0原文

当我尝试运行以下查询时：

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

它不起作用，因此我必须使用 $date = date("Ymd", strtotime('+10 hours'))

但为什么 date_add 不起作用？

非常感谢

原文

When I try to run the following query:

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

It doesn't work, so I have to use the $date = date("Y-m-d", strtotime('+10 hours'))

But why date_add doesn't work?

Thanks much

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

月下伊人醉 2024-10-07 07:50:03

请注意 DATE_ADD( ) 函数除了日期之外还返回时间部分。这与使用 php 的 date("Ymd", 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)

Note that DATE_ADD() function returns a time part in addition to the date. This is not the same as using php's date("Y-m-d", strtotime('+10 hours')), which only returns the date part.

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)

You could use WHERE date = DATE(DATE_ADD(NOW(), INTERVAL 10 HOUR)) instead:

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)

Test case:

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)

回复收藏 0 原文

盗梦空间 2024-10-07 07:50:03

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)

NOW() is a timestamp, not a date.

Example:

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

I think this is what you want:

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

回复收藏 0 原文

秋日私语 2024-10-07 07:50:03

您将 DateTime（DATE_ADD 的结果）与 DATE 进行比较。您需要WHERE date = DATE(DATE_ADD(NOW(), INTERVAL 10 HOUR))

回复收藏 0 原文

心如荒岛 2024-10-07 07:50:03

这：

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

...确实有效（假设date是DATETIME），但它包括执行语句时的时间部分——date`值需要与< em>完全要返回。

This:

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

...does work (assuming date is DATETIME), but it includes the time portion when the statement was executed -- thedate` values need to match that exactly to be returned.

回复收藏 0 原文