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