MySQL date and datetime formats unrecognised by Data Studio

user_2404798 0
Viewed 5466

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):

  • Validated_date datetime 2017-09-27 12:31:04
  • Expiration_date date 2017-12-24

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

  • Validated_date Date Hour (YYYYMMDDHH)
  • Expiration_date Date (YYYYMMDD)

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?

google-data-studio date datetime mysql
4 Answers

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

user_45087670

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

user_140499880
Related