David542 David542 - 3 years ago 68
SQL Question

Searching dates in BigQuery

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
.

Answer Source

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  
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download