results for last 30 days in bigquery
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);
Using Google's own documentation, it would appear you need to reverse the parameters in the DATE_ADD() function. The function should be called with three parameters as follows:
DATE_ADD(<timestamp>,<interval>,<interval_units>)
with interval units being the kind of time unit you're referencing (day, hour, year etc.)
The reason you're getting the error you are is that Google is trying to convert day
into some sort of date
format.
Source: Google Query Reference
Quoted Text:
DATE_ADD(timestamp,interval, interval_units)
Adds the specified interval to a TIMESTAMP data type. Possible interval_units values include YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. If interval is a negative number, the interval is subtracted from the TIMESTAMP data type.
Example:
SELECT DATE_ADD(TIMESTAMP("2012-10-01 02:03:04"), 5, "YEAR");
Returns: 2017-10-01 02:03:04 UTC
SELECT DATE_ADD(TIMESTAMP("2012-10-01 02:03:04"), -5, "YEAR");
Returns: 2007-10-01 02:03:04 UTC