MySQL database based bug tracking software
Bugzero one-for-all bug tracking and help desk support system
Q: MySQL SQLException: Cannot convert value '0000-00-00 00:00:00' to TIMESTAMP.

A: The stacktrace is
   Caused by: java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 3 to TIMESTAMP.
	at com.mysql.jdbc.ResultSet.getTimestampFromString(
	at com.mysql.jdbc.ResultSet.getTimestampInternal(
	at com.mysql.jdbc.ResultSet.getTimestamp(
	... 40 more

By default, the TIMESTAMP column is defaulted to the current time when it is not set explictly or set to null.
However, if the TIMESTAMP column is allowed NULL and defaulted to '0000-00-00 00:00:00', then JDBC driver
(JConnector) 3.1 or later will throw an exception when converting this value to a timestamp.

To fix this problem, first do (for pre-version 6.0 of Bugzero)

mysql> desc reminder;

and make sure you have something like

| remind_ts     | timestamp    | NO   |     | CURRENT_TIMESTAMP |       | 

Then, do 

mysql> update reminder set remind_ts=null where remind_ts='0000-00-00 00:00:00';

This shall set the value to the current time.

For version Bugzero 6 and after, do
mysql> desc person;
and if you have
| ts     | timestamp    | NO   |     | CURRENT_TIMESTAMP |       | 
Then, do 

mysql> update person set ts=null where ts='0000-00-00 00:00:00';

Another option is, if you are using JConnector 3.1.4 or later, to add a parameter


in the jdbc url.

(This exception shall not happen for JConnector 3.0 or earlier, or Bugzero v6.5 or later).

* Reference brought to you by Bugzero, it's more than just defect tracking software!

Home - FAQs