CODEWITHSUNDEEP
sqlgoogle-bigquery
Jaskaran Singh Puri
Jaskaran Singh Puri

Reputation: 739

BigQuery how to query partition by month/year when table partitioned by day?

I partitioned a table using the following command on the date field and it got partitioned by day by default. 

CREATE TABLE `sales.sales_txn_partition` PARTITION BY DateOfTxn CLUSTER BY SalesPersonID AS (SELECT * FROM `sales.sales_records` WHERE SalesDate IS NOT NULL)

enter image description here

Now, I want to filter based on year/month as well in my WHERE clause instead of writing the complete date. Is it possible or I'd need to create new partitioned table for year/month separately?

I'm using the following command, nothing works

SELECT * FROM `sales.sales_txn_partition` WHERE DATE_TRUNC(EXTRACT(DATE FROM _PARTITIONTIME), MONTH) = 2018; 
## Doesnt work: Query error: Unrecognized name: _PARTITIONTIME at [3:78]

SELECT * FROM `sales.sales_txn_partition` WHERE EXTRACT(YEAR from DateOfTxn)=2018;
## Works but scans the full table

Upvotes: 0

Views: 1268

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

Why not just use direct comparisons?

where DateOfTxn >= date('2018-01-01') and
      DateOfTxn < date('2019-01-01')

Upvotes: 1

Related Questions