I am trying to build a query for a tableau dashboard that is connected to Google BigQuery. We have tables for each month of data, but I want to present the last 30 days of data at any given time (so it will have to go across multiple tables). The current query I have gives the error "Timestamp literal or explicit conversion to timestamp is required." I've been looking around for some help on how to convert to timestamp but haven't found anything helpful. This is my code.
SELECT
DATE(date_time) AS date,
FROM
TABLE_QUERY(myTable, "date(concat(left(table_id,4),'-',right(table_id,2),'-','01')) >= '2017-06-01'")
WHERE
DATE(date_time) >= DATE_ADD(day,-30, current_date())
and DATE(date_time) <= current_date()
ORDER BY
date
Any help would as to how to get it to work will be greatly appreciated.
Note: we are using legacy SQL
For anyone looking to do the same thing with BigQuery Standard SQL, this will return the last 30 days of data based on the current date.
https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date_add
SELECT
date,
FROM
`<PROJECT>.<DATASET>.<TABLE>`
WHERE
Date >= DATE_ADD(CURRENT_DATE(), INTERVAL -30 DAY);