Data Studio
2K members online now
2K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

Data Studio Time Dimension and Big Query seems to issue strange query

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

In Big Query, I have a column in epoch in seconds, when I connect that to Data Studio as Time Dimension as (YYYYMMDD), it issues a query to BigQuery that will give null for the column. Is the query generation incorrect when Big Data tries to use a column with epoch in seconds?

 

Big Data issue the following:

 

SELECT COUNT(t0.ID) AS t0.calc_MVQ379 FROM [REDACTED] AS t0 WHERE (STRFTIME_UTC_USEC(TIMESTAMP_TO_USEC(TIMESTAMP(STRING(t0.Requested*1000000))), '%Y%m%d') >= '20160912' AND STRFTIME_UTC_USEC(TIMESTAMP_TO_USEC(TIMESTAMP(STRING(t0.Requested*1000000))), '%Y%m%d') <= '20161011') ORDER BY t0.calc_MVQ379 ASC;

 

If i issue this in big query, i will get 

 

SELECT STRFTIME_UTC_USEC(TIMESTAMP_TO_USEC(TIMESTAMP(STRING(t0.Requested*1000000))), '%Y%m%d') FROM [REDACTED] AS t0

 

I will get null,

 

If i try the following: 

 

SELECT FORMAT_UTC_USEC(t0.Requested*1000000) FROM [REDACTED] AS t0

 

I will get a reasonable timestamp like 2016-10-05 21:24:17.000000

 

 

Data Studio Time Dimension and Big Query seems to issue strange query

Explorer ✭ ✭ ✭
# 2
Explorer ✭ ✭ ✭

I was running into similar issues and discovered that I needed to change the date column format in the original data source to YYYYMMDD in order to get Data Studio to recognize the Dates properly. I also had to add the date column in Bigquery as a String instead of a Date, Datetime or Timestamp. 

It's also worth mentioning that this fix was specific to tables created from Google Sheets.  When I upload csv files to create a table in BQ, the date can column can have YYYY-MM-DD and other standard Timestamp formats, added as a Date or Timestamp column in BQ and Data Studio is ok with it.

So I'm not sure how many different table types have date compatibility issues in Data Studio, and I'm not sure if the fix I mentioned will work for all of them, but I know that you can fix federated tables by either adding a column with date format YYYYMMDD or re-formatting the existing date column.