Skip to main content
Anonymous
Asked a question 6 months ago

Value '0000-00-00 00:00:00' in Google Data Studio: When trying to connect to a new client that was added yesterday to the dash platform I received this error message. Failed to execute connection with error: Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp in Google Data Studio. What's happening and how do I fix it?

have a question?  ask it now and either we will answer it or one of you fellow community members.

This error is caused by the "0" timestamps that are sometimes stored as values in DateTime fields within the database tables. Basically MySQL can handle "0" timestamps but Google Data Studio can't resolve them using Java. The culprit is typically a field in the tables called "dtUpdatedOn", which has a zero value for a past date(s). So the data is typically available, GDS just can't retrieve it. 

The best fix we've found thus far is to use a custom query naming the fields you want to retrieve from the table and excluding that particular "dtUpdatedOn" field. 

We will also be working on a fix within the db to see if we can by default populate some value for the field that GDS can resolve so you don't have to use the modified query. Here's an example query you can use insteadn (sellermonthmetric table example):

SELECT s.DateKey, s.SellerID, s.ProfileID, s.MarketPlaceID, s.MarketPlaceName, s.DataSource, s.DateTime, s.SellerName, s.MerchantType, s.AmazonSellerID, s.SPAAdClicks, s.SPAAdImpressions, s.SPAAdSpend, s.SPAAdSales, s.SPAAdOrders, s.HeadlineAdClicks, s.HeadlineAdImpressions, s.HeadlineAdSpend, s.HeadlineAdSales, s.HeadlineAdOrders, s.SDAdClicks, s.SDAdImpressions, s.SDAdSpend, s.SDAdSales, s.SDAdOrders, s.SPACTR, s.SPACPC, s.SPAACoS, s.HeadlineCTR, s.HeadlineCPC, s.HeadlineACoS,s.SDCTR, s.SDCPC, s.SDACoS
FROM sellermonthmetric s
WHERE (s.SellerName = "YourMerchantName" OR s.SellerName = "YourMerchantName")
AND s.DateTime >= DATE_FORMAT(CURDATE(), '%Y-%m-01') - INTERVAL 2 MONTH
AND s.DateTime < DATE_FORMAT(CURDATE(), '%Y-%m-02')