results for last 30 days in bigquery

Question

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

user_9746982 · Answer

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
  `..` 
WHERE
  Date >= DATE_ADD(CURRENT_DATE(), INTERVAL -30 DAY);

user_17107842 · Answer

The format has changed as per google's documentation.

SELECT DATE_ADD(DATE "2008-12-25", INTERVAL 5 DAY) AS five_days_later;

results for last 30 days in bigquery

3 Answers