How to custom filter a MySQL data source by date range in Looker Studio?

Question

I am using Looker Studio to make a dashboard from a MySQL data source. I have added a Date Range control and a little table to display the data from the MySQL database.

My table has roughly this schema:

MyTable {
  date DATETIME
  value INTEGER
}

Now, I wish to do a filter similar to this:

SELECT * FROM MyTable
  WHERE date <= DATE_RANGE_END

What should I replace DATE_RANGE_END with if I want to take the end date of the Date Range control?

I'm aware of the Date Range Dimension option, but that seems to filter out the entire table where date is between the values of the Date Range control, but I want to only take into account the end date.

I have tried to set the query to:

SELECT * FROM MyTable
  WHERE date <= PARSE_DATE('%Y%m%d', @DS_END_DATE)

But it seems that this is only valid for BigQuery and I'm using simply MySQL.

Is there a way to achieve this? Or am I forced to migrate my data to BigQuery?

Answer 1

So I found a solution that works well enough for my use case. I have created an external connection in BigQuery to my MySQL database to be used for federated queries.

Finally, the query that I planned on running, which was:

SELECT * FROM MyTable
  WHERE date <= PARSE_DATE('%Y%m%d', @DS_END_DATE)

Turned into:

SELECT * FROM EXTERNAL_QUERY(
  "connection_id",
  "SELECT * FROM MyTable;"
)
  WHERE date <= PARSE_DATE('%Y%m%d', @DS_END_DATE)

---

While this works, strictly speaking I'd say it is not too performant since BigQuery is querying the entire table from MySQL and then filtering it later. But for small tables, this seems to work really well and at least saves me the effort of having to sync/migrate MySQL to BigQuery.