24 Feb 19 · victor · #Bigquery ·   Bookmark   ×

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

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds