Selecting empty mysql datetime fields

Question

Is there a better way to select empty datetime fields than this?

SELECT * FROM `table` WHERE `datetime_field` = '0000-00-00 00:00:00'

Answer 1

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());

Answer 2

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.

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:

SELECT * FROM `table` WHERE `datetime_field` IS NULL

That's what I'd do with a standards-compliant DBMS. My understanding is that MySQL may represent true NULL datetime fields in this horrific manner, in which case I guess the IS NULL may not work.

Answer 3

I use this:

SELECT * FROM `table` WHERE UNIX_TIMESTAMP(`datetime_field`) = 0

Answer 4

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')