I recently ran into an issue where MySQL select queries were failing, and it turned out there was invalid data in a timestamp column in the table, specifically values of 0000-00-00 00:00:00.
You would think you can use the following query to find all the rows with invalid data in the timestamp column.
SELECT * FROM `table_name` WHERE `timestamp_column` = '0000-00-00 00:00:00';
But the above will fail with an error related to an invalid timestamp value. The solution is to use a cast to convert the timestamp column to a string, and then compare it to the string
SELECT * FROM `table_name` WHERE CAST(`timestamp_column` AS CHAR(20)) = '0000-00-00 00:00:00';
Once you have the rows you want to remove, you can use the following query to update them to NULL,
or replace NULL with NOW() or CURDATE() or whatever you want.
UPDATE `table_name` SET `timestamp_column` = NULL WHERE CAST(`timestamp_column` AS CHAR(20)) = '0000-00-00 00:00:00';