13

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?

Alex Mille
  • 133
  • 1
  • 8

4 Answers4

14

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.

Gegenwind
  • 1,388
  • 1
  • 17
  • 27
  • 2
    That should be explained in Google's date format help, worked perfectly, upvoted. – drublackberry Dec 25 '17 at 22:29
  • 2
    pretty disappointing that data studion still doesn't support cloud sql timestamp and datetime objects without this explicit conversion – uwe Feb 24 '19 at 03:40
0

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

Bobbylank
  • 1,906
  • 7
  • 15
0

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

Carmoreno
  • 1,271
  • 17
  • 29
0

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

Steffen Moritz
  • 7,277
  • 11
  • 36
  • 55
Hamilton Tenório da Silva
  • 1