02 Mar 19 · victor · #bigquery ·   Bookmark   ×

How to fetch all the records of last 12 months in Google Bigquery

Read all the records based on a date column

To read all the records from last 12 months:

SELECT * FROM `project.dataset.table`
WHERE load_date BETWEEN 
DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH) AND CURRENT_DATE() 

Read all the records based on a timestamp column

To read all the records from last 12 months:

SELECT * FROM `project.dataset.table`
WHERE DATE(load_ts) BETWEEN
DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH) AND CURRENT_DATE();

Best Practice ⚡

  • When possible filter based on the Partition column in Bigquery to get the best performance as well as cost benefits

victor

posted on 02 Mar 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