CODEWITHSUNDEEP
sqlgoogle-bigquerylooker-studio
Nicky K
Nicky K

Reputation: 45

Standard SQL query returning correct results in BigQuery but not in Data Studio

I need to return a list of names which have parameters which fall between 2 dates. I have hard-coded two dates as part of the data verification. My sql query in BigQuery is as follows:

declare DS_START_DATE FLOAT64;
declare DS_END_DATE FLOAT64;

SET DS_START_DATE = 1578390532050;
SET DS_END_DATE =  1578391211289;

SELECT DISTINCT
  Name AS block_names
FROM
  my_data_source
LEFT JOIN
  UNNEST (holes) AS n_holes
ON
  1=1
  WHERE
  (n_holes.LastModifiedHoleDate ) > CAST(DS_START_DATE as FLOAT64)
  AND n_holes.LastModifiedHoleDate < CAST(DS_END_DATE as FLOAT64)

Note: the DS_START_DATE and DS_END_DATE are both in UNIX time.

So basically, I am querying for results that were modified on the 7th of January, which will return only one result.

The above query return only one result, which is correct.

I have changed the format of the query slightly so that I can use it in the connection to my table in BigQuery from Data Studio:

SELECT DISTINCT
  Name AS block_names,
n_holes.LastModifiedHoleDate as LM
FROM
  my_data_source
LEFT JOIN
  UNNEST (holes) AS n_holes
ON
  1=1
  WHERE
  (n_holes.LastModifiedHoleDate ) >= CAST(@DS_START_DATE as FLOAT64)
  AND n_holes.LastModifiedHoleDate <= CAST(@DS_END_DATE as FLOAT64)

I have enabled the date parameters in my Data Studio data source, and have finished creating the data source.

enter image description here

I have then made a test report using the above data source for the report. I simply have a date range control and a chart on my report.

No matter what range I choose on the date range, I get two results on the chart, where I should only get one.

enter image description here

Normally the tables and charts I have used in Data Studio have the option to select a date range dimension which links the data to the date range selected on the date time picker:

The table that I have added to this report has no such option. I am assuming that this is correct because we are using a data source that needs a start and end date?

The parameters option in the chart has nothing in it:

enter image description here

Again, I am assuming that Data Studio doesn't need parameters specified because I am using start and end dates.

What have I missed that my date range picker is not affecting the data displayed in my chart? It seems like it is linked to the chart automatically, but the results are wrong.

Many thanks in advance!

Upvotes: 1

Views: 1218

Answers (1)

Daniel Duato
Daniel Duato

Reputation: 378

You need to convert @DS_START_DATE and @DS_END_DATE into a Unix timestamp if you want to compare them with one. For me this did the trick:

WHERE
n_holes.LastModifiedHoleDate >= UNIX_MILLIS(PARSE_TIMESTAMP('%Y%m%d', @DS_START_DATE))
AND n_holes.LastModifiedHoleDate <= UNIX_MILLIS(PARSE_TIMESTAMP('%Y%m%d', @DS_END_DATE))

I hope it also works for you!

Upvotes: 1

Related Questions