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(ResultSet.java:6369)
	at com.mysql.jdbc.ResultSet.getTimestampInternal(ResultSet.java:6394)
	at com.mysql.jdbc.ResultSet.getTimestamp(ResultSet.java:2268)
	... 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

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 xxxx;

This shall set the value to the current time.


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

zeroDateTimeBehavior=convertToNull

in the jdbc url.

(This exception shall not happen for JConnector 3.0 or earlier).




* Reference brought to you by 

Bugzero, it's more than just defect tracking software!



Home -
FAQs