CODEWITHSUNDEEP
mysqldatedatetimelooker-studio
Alex Mille
Alex Mille

Reputation: 133

MySQL date and datetime formats unrecognised by Data Studio

I've linked Google data studio with a MySQL database using the standard connector. Almost everything works fine except for date and datetime fields.

I have these 2 fields in phpmyadmin (field name, field type, output):

In Data Studio I've set these types, but none of them are recognised:

I tried to format the field with date_format in my SELECT:

DATE_FORMAT(p.Expiration_date, '%Y%m%d') AS "Expiration Date"

I even tried other date_formats but they're never recognised as dates in Data Studio: 

DATE_FORMAT(p.Expiration_date, '%Y/%m/%d') AS "Expiration Date"
DATE_FORMAT(p.Expiration_date, '%Y-%m-%d') AS "Expiration Date"

Any idea?

Upvotes: 13

Views: 6011

Answers (4)

Hamilton Tenório da Silva
Hamilton Tenório da Silva

Reputation: 1

I had the same issue. For me, the only solution was create a formula this way:

CONCAT(SUBSTR(field, 1, 4), SUBSTR(field, 6, 2), SUBSTR(field, 9, 2))

and use this as AAAAMMDD format

Upvotes: 0

Carmoreno
Carmoreno

Reputation: 1319

I have used the follow formule and working for me:

TODATE(yor-field-from-mysql, '%Y-%m-%dT%H:%M:%S', "%Y%m%d%H%M")
Then I choose ***YYYYMMDDhhmm*** format in DataStudio. I hope it helps.

Edit: In this case, date came from javascript using moment.js

Upvotes: 0

Bobbylank
Bobbylank

Reputation: 1946

I've never tried directly with Data Studio but when I extract datetime fields from mySQL to use in BigQuery I use:

CONVERT(DATETIME2(0), [DATETIME_FIELD])

This removed any milliseconds which generally cause problems and convert it to a recognisable datetime format for Google

Upvotes: 0

Gegenwind
Gegenwind

Reputation: 1428

I had the same issue. My approach to solve this is to modify the date format within Google Data Studio by creating a new dimension, reformatting the mySQL Datetime into the desired format.

In your example you use DATE_FORMAT. I wonder if you apply this in Data Studio (which does not know DATE_FORMAT) or in mySQL?. If you do it in data studio and leave your mySQL/phpmyadmin untouched you can use this:

TODATE(your-date-field, 'DEFAULT_DASH', '%Y%m%d')

This will take the date format YYYY-MM-DD with optional time in HH:ii:ss and reformat it into YYYYMMDD which works with data studio.

Upvotes: 14

Related Questions