Search code examples
mysqldatedatetimelooker-studio

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

  • 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?

Solution

  • 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.