php 选择空的mysql日期时间字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/559590/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Selecting empty mysql datetime fields
提问by Callum
Is there a better way to select empty datetime fields than this?
有没有比这更好的选择空日期时间字段的方法？
SELECT * FROM `table` WHERE `datetime_field` = '0000-00-00 00:00:00'
回答by paxdiablo
Better in what way? That query does everything you ask of it and, provided there's an index on datetime_field, it's as fast as it's going to get.
用什么方式更好？该查询会执行您要求的所有操作，并且如果在 上有索引datetime_field，它就会尽可能快地获得。
If you're worried about the query looking "ugly", don't be. Its intent is quite clear.
如果您担心查询看起来“难看”，请不要担心。其意图十分明确。
The only possible improvement you could consider is to use NULLs for these zero-date/time rows, in which case your query becomes:
您可以考虑的唯一可能的改进是对这些零日期/时间行使用 NULL，在这种情况下，您的查询变为：
SELECT * FROM `table` WHERE `datetime_field` IS NULL
That's what I'd do with a standards-compliant DBMS. My understanding is that MySQL mayrepresent true NULL datetime fields in this horrific manner, in which case I guess the IS NULLmay not work.
这就是我对符合标准的 DBMS 所做的事情。我的理解是 MySQL可能以这种可怕的方式表示真正的 NULL 日期时间字段，在这种情况下，我猜这IS NULL可能不起作用。
回答by Orlando Colamatteo
SELECT * FROM db.table WHERE UNIX_TIMESTAMP(datetime_field) = 0
SELECT * FROM db.table WHERE UNIX_TIMESTAMP(datetime_field) = 0
I like this code because it not only catches "zero dates" (e.g. '0000-00-00 00:00:00') is also catches "zeroes in dates" (e.g. '2010-01-00 00:00:00')
我喜欢这段代码，因为它不仅可以捕获“零日期”（例如“0000-00-00 00:00:00”）还可以捕获“日期中的零”（例如“2010-01-00 00:00:00”） )
回答by WiseOwl9000
Provided your date/datetime column is NOT NULL you can just use:
如果您的日期/日期时间列不是 NULL，您可以使用：
SELECT * FROM `table` WHERE `datetime_field` IS NULL
MySQL will select columns that equal '0000-00-00 00:00:00' for you.
MySQL 将为您选择等于 '0000-00-00 00:00:00' 的列。
Note: If you do:
注意：如果你这样做：
SELECT `datetime_field` IS NULL
MySQL will return 0 (false), but this statement will be true when used as part of the WHERE clause.
MySQL 将返回 0（假），但当用作 WHERE 子句的一部分时，该语句将为真。
From the manual
从手册
For DATE and DATETIME columns that are declared as NOT NULL, you can find the special date '0000-00-00' by using a statement like this:
SELECT * FROM tbl_name WHERE date_column IS NULLThis is needed to get some ODBC applications to work because ODBC does not support a '0000-00-00' date value.
对于声明为 NOT NULL 的 DATE 和 DATETIME 列，您可以使用如下语句找到特殊日期“0000-00-00”：
SELECT * FROM tbl_name WHERE date_column IS NULL这是使某些 ODBC 应用程序工作所必需的，因为 ODBC 不支持“0000-00-00”日期值。
回答by olvlvl
Or you can simply do :
或者你可以简单地做：
SELECT * FROM `table` WHERE `datetime_field` = 0
回答by Stiropor
I use this:
我用这个：
SELECT * FROM `table` WHERE UNIX_TIMESTAMP(`datetime_field`) = 0
回答by brechmos
As another option, I have seen:
作为另一种选择，我已经看到：
SELECT * FROM `table` WHERE YEAR(`datetime_field`)=0
回答by Suroot
Let me put out a disclaimer, I'm not sure what this will do. But it would be really cool if it worked.
让我发表一个免责声明，我不确定这会做什么。但如果它有效，那将是非常酷的。
SELECT * FROM table WHERE datetime = DATESUB(NOW(),NOW());
