BigQuery timestamp current date minus x days

Question

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

Translative

Answer 1

You are missing TIMESTAMP_ADD():

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

Eruptive

Worked like a charm! Thank you, I have accepted the answer. – Translative

Answer 2

It's also possible to use TIMESTAMP_SUB()

where created_time > TIMESTAMP_SUB(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), INTERVAL 5 DAY)

Diagonal