CODEWITHSUNDEEP
sqlgoogle-bigquery
David542
David542

Reputation: 110592

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.

Upvotes: 0

Views: 161

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

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

Related Questions