Reputation: 110592
I have a date field stored in BigQuery. I would like to search on matching the month or year or entire date. What would be the most performant way to do the following?
# An example date looks like "2014-01-01"
# By year
SELECT * FROM index WHERE date LIKE '2014-%'
# By month
SELECT * FROM index WHERE date LIKE '%-01-%'
# By day
SELECT * FROM index WHERE date LIKE '%-\d\d-01'
# By year and month
SELECT * FROM index WHERE date LIKE '2014-01-%'
How would I do the above searches in the most performant way in BigQuery?
Note, the above field is of type DATE
.
Upvotes: 0
Views: 161
Reputation: 173210
for BigQuery Standard SQL and assumes that date field stored as DATE type
By year
#standardSQL
SELECT * FROM index WHERE EXTRACT(YEAR FROM dt) = 2014
By month
#standardSQL
SELECT * FROM index WHERE EXTRACT(MONTH FROM dt) = 1
By day
#standardSQL
SELECT * FROM index WHERE EXTRACT(DAY FROM dt) = 1
Upvotes: 2