Search code examples
datetimegoogle-bigquerytimestamp

BigQuery timestamp current date minus x days

I have a TIMESTAMP column with dates that I need to filter. I need to grab the data that is 5 days old. So current date - 5 days. My data is in BigQuery. I tried the following query:

where created_time >= (TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), INTERVAL -5 DAY)

I got an error: Unexpected INTERVAL expression

Solution

  • You are missing TIMESTAMP_ADD():

    where created_time > TIMESTAMP_ADD(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), INTERVAL -5 DAY)