0

I've linked Google data studio with a MySQL database using the standard connector. Everything works except sorting correctly by date.

Database columns configured like this:

  • price decimal(15,2) not null 24.59
  • last_changed timestamp not null 2019-03-25 19:24:52

GDS datasource fields configured (as per this answer) like this:

  • last_changed Date(YYYYMMDD) 2019-03-25
  • sold_at GDS function TODATE(last_changed, 'DEFAULT_DASH', '%Y%m%d')

GDS bar chart configured like this:

  • time dimension sold_at as date
  • dimension sold_at as date
  • value metric price as sum
  • sorting sold_at as date ascending
  • time range auto from date picker with default set to current month

I am experiencing the following issue: When sorting is set to sold_at the bar chart is empty with the default date range. Only when i go back at least one month it displays values in the chart but also cuts off the last day so that its less than it should be. Only when sorting is set to price it displays everything correctly, even in the current month. Also when switching from sorting set to price to sold_at while the default time range is selected it shows the sold_at field under Invalid in the sorting modal.

I tried modifying the database column to datetime instead of timestamp but it didnt change anything. I also tried setting a different default time range but that doesnt change anything also. It keeps showing no data for 1 month back.

Any idea or someone who already stumbled across this scenario?

Nikolas Schröter
  • 1
  • 1
  • 3
  • look into the mysql logs to see what actually is send to the database there you also can see what happens when you change the settings. – nbk Jun 22 '19 at 21:13
  • @nbk Yeah, i've had the same idea just before you commented. Seems like GDS sets the select limit to 100,000 entries so im missing about 7000 and thats why im seeing these issues. Do you have any idea on how to prevent GDS from doing that? – Nikolas Schröter Jun 22 '19 at 22:05
  • @nbk solved, thanks for the tip nonetheless! – Nikolas Schröter Jun 22 '19 at 22:48

1 Answers1

0

For people that come across the same issue:

This issue was caused by the MYSQL timestamp/datetime column having optional Hrs:Min:Sec. This caused the GDS mysql query to not be able to group them by day and thus resulting in more than 100000 rows, which is more than the hardcoded limit set by the connector.

Solution: Added another timestamp/datetime column and filled it with

UPDATE table SET newcolumn = DATE_FORMAT(oldcolumn, '%Y/%m/%d');

It now works like a charm.

Nikolas Schröter
  • 1
  • 1
  • 3