Binding with Infamous 0000-00-00 in MySQL via JDBC

  Sometimes you are dealing with a poor maintained MySQL server. It may contain some dirty data, including the infamous datetime 0000-00-00 00:00:00 or date 0000-00-00. First of all, if you are developing a new service, just don't use the value 0000-00-00 as a date. Or you are running an existed service and have the privileges, write a script map them into NULL or 1970-01-01. Zero dates cause a lot of problems, some of them may not solvable with this post.

  Back to the topic, when you read a 0000-00-00 from MySQL as a java.sql.Date, it raises an error '0000-00-00' can not be represented as java.sql.Date.
  JDBC has provided a configuration to solve this problem, which is

zeroDateTimeBehavior=convertToNull

  When you are connecting MySQL server, you may specify this configuration in the URL, like mysql://user:pass@host:port/db?zeroDateTimeBehavior=convertToNull. With this configuration, JDBC automatically transforms your zero dates/datetimes to 1970-01-01 (00:00:00).

  There are some space left, so let me talk about the problems you are going to face if you continue to use zero dates.
  The first is the cause of this post, reading with program. Zero dates are obviously invalid date to any formats. When you use most dataframe framework, it would cause an error the above.
  Second, transferring database. If you want to migrate to PostgreSQL(or any other popular database), it would be an inevitable problem. Since PostgreSQL do not allow zero dates in it (and most database do not). My solution is creating a view foreach table which map the zero dates to NULL or 1970-01-01, then export them to the new database.
  Third, binding data visualize software or analysis software. Of course most commercial software would deal with the zero dates problem. The real problem is, reading a date 0000-00-00 doesn't make any sense to marketing or decision team. You would have to filter them or transform them to an explainable report, so why not do it when designing the table schema?

Reference:
  zeroDateTimeBehavior