Mysql DATETIME评估：获取其值在当天午夜之前的所有记录（基本上是昨天和之前

This is really simple yet I always struggle with it. I need help getting records before midnight:

这很简单,但我总是很努力。我需要帮助在午夜之前获取记录:

 AND last_checked < date('2013-06-25 00:00:00'))

This obviously doesn't work, since its string evaluation. I do not want to restrict it to this year and put a between in the code. Any help is extremely appreciated :)

这显然不起作用,因为它的字符串评估。我不想将它限制在今年,并在代码中加入。非常感谢任何帮助:)

3 个解决方案

#1

You can also do this in a generic way

您也可以通用方式执行此操作

AND last_checked < ( DATE(NOW()) + INTERVAL 0 SECOND );

Watch this:

mysql> SELECT DATE(NOW()) + INTERVAL 0 SECOND Midnight;
+---------------------+
| Midnight            |
+---------------------+
| 2013-06-25 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql>

#2

You should be able to just do

你应该能做到的

AND last_checked < '2013-06-25 00:00:00'

Using the date() function just extracts the date part of the argument.

使用date()函数只提取参数的日期部分。

#3

If last_checked is of datetime data type, then your WHERE clause will look like this:

如果last_checked是datetime数据类型,那么WHERE子句将如下所示:

WHERE ... 
    AND cast (last_checked as date) = '2013-06-25'

CAST (datetime as date) drops time part, so you can easily get all data between 00h:00m:00s and 23h:59m:59s .

CAST(日期时间为日期)会丢弃时间部分,因此您可以在00h:00m:00s和23h:59m:59s之间轻松获取所有数据。

#1