"Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp" when connected to RDS mySQL DB on Google Data Studio Report.

Question

I've successfully connected the RDS Database as a Data Source on Google Data Studio. The tables and fields within the tables are showing fine with the correct data types as well.

But when trying to create a Report using this data source, the Time Dimension dimension gives error :

Failed to execute connection with error: Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp

I tried adding zeroDateTimeBehavior=convertToNull to the database name field when connecting but Google Data Studio does not accept the connection.

Any idea how to get this to work when some timestamps are 0000-00-00 00:00:00?

Answer 1

You can try to solve it using SQL, but you'll lost some data. For example:

SELECT * FROM table
WHERE date != 0;