Timestamp query issue when using Google Data Studio community connector with BigQuery

Question

I'm having an issue where Google Data Studio is sending bad timestamp data to my community connector, so that when I try and filter on a date, I get an error. I have two BigQuery TIMESTAMP type fields (named timestamp and created_at), both being passed through to my community connector without modification. Once I add a date filter to the reports (in order to do time series, or regular filtering), like so:

then my queries from the connector (viewed in my BigQuery project history) begin to fail like this:

Could not cast literal "20200825" to type TIMESTAMP at [1:677]

The query in BigQuery looks something like this:

SELECT t0.created_at, SUM(t0.sum_metric) AS t0_qt_1z4br3iwbc FROM (SELECT field1, field2, field3) from data_name.table_name where user_identifier in (2)) AS t0 WHERE (t0.created_at >= '20200825' AND t0.created_at <= '20200831') GROUP BY t0.created_at ORDER BY t0.created_at ASC;

This really feels like a bug with the community connector regarding BigQuery. Is there some way around this? Am I just doing something wrong I'm not seeing?

I tried removing the 'created_at' field, so I just had one timestamp, and it does not work. Note that the same query works when the dates are hyphenated like: 2020-08-25 — Gunner

Gunner Gunner · Accepted Answer · 2020-09-01T22:03:28

Ok, I solved this. In your community connector you'll need to add this to your config:

config.setDateRangeRequired(true);

this will send a startDate and endDate parameter with your getData request (it defaults to 28 days). Access them in getData() like so:

var startDate = request.dateRange.startDate;
var endDate = request.dateRange.endDate

and then use them in your query as necessary.

As a side note, if you're storing a timestamp field in Google Data Studio and making a community connector, you'll need to set up a calculated field so that the reports treat it appropriately (display a human readable date instead of a timestamp). I'm first reading the timestamp field out of the database as a string like: STRING(timestamp, 'UTC') AS timestamp and then using this value to create a dimension. This is done in the schema like the following (use parsing appropriate to your field if it's different):

fields.newDimension()
  .setId('date_timestamp')
  .setName('date_timestamp')
  .setDescription('Timestamp as a date.')
  .setFormula("TODATE($timestamp, '%Y-%m-%d %H:%M:%S%z', '%Y%m%d')")
  .setType(types.YEAR_MONTH_DAY)

Timestamp query issue when using Google Data Studio community connector with BigQuery

1 Answers