10

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

kshoe94
  • 389
  • 2
  • 4
  • 11
  • 1
    Just wondering, is there a reason for not using the recommended [standard](https://cloud.google.com/bigquery/docs/reference/standard-sql/) version? – Willian Fuks Jul 24 '17 at 20:17
  • @WillianFuks my company has not migrated to using Standard version so I am stuck using legacy. – kshoe94 Jul 25 '17 at 20:46
  • I highly recommend investing some time for the migration. We did the same at our company and it was 100% worth it. – Willian Fuks Jul 25 '17 at 22:10

3 Answers3

29

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);
Mitch
  • 356
  • 3
  • 4
4

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

Cowthulhu
  • 528
  • 2
  • 8
  • 21
  • 1
    That was definitely one of my issues, but I also realized that I needed to cast all the dates involved in the DATE_ADD function and comparisons to Timestamps. Thanks for the help – kshoe94 Jul 24 '17 at 20:09
0

The format has changed as per google's documentation.

SELECT DATE_ADD(DATE "2008-12-25", INTERVAL 5 DAY) AS five_days_later;
Mureinik
  • 297,002
  • 52
  • 306
  • 350
Prasana V
  • 1