How to SELECT from a timestamp column between now and n days ago in Google Bigquery

Problem 🔍

• Fetch all the rows between two timestamps
• Fetch all the rows between two date values

Solution 💡

#1 Fetch all rows that are in between now and 100 days ago

Use timestamp functions:

SELECT * FROM `my-project.my_dataset.table` t
WHERE transaction_timestamp BETWEEN 
TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -150 DAY) AND CURRENT_TIMESTAMP())

The column and the values used in the BETWEEN Clause has to be of the same datatype. If not, it will throw the below error:

Error: No matching signature for operator BETWEEN for argument types: TIMESTAMP,
DATE, DATE. Supported signature: (ANY) BETWEEN (ANY) AND (ANY) at [6:17]

#2 Fetch all rows that are in between now and 100 days ago

Using Date functions:

SELECT * FROM `my-project.my_dataset.table` t
WHERE transaction_date BETWEEN 
DATE_ADD(CURRENT_DATE(), INTERVAL -150 DAY) AND CURRENT_DATE())

The column and the values used in the BETWEEN Clause has to be of the same datatype

victor

posted on 24 Feb 19