filter by first 4 digits of a timestamp value in BigQuery standard SQL

0
votes

I have a variable where the entries are date (timestamp). The exact format is like this: 2009-03-01 00:00:00 UTC.

I want to filter all the rows where year is 2009 (first 4 digits of the timestamp). I am using google BigQuery standard SQL. I tried the following:

WHERE LEFT(CAST(incurred_month_timestamp as STING), '4') LIKE 2013

If anyone can share the query, it would be helpful.

Thanks.

2
sqlgoogle-bigquerybigquery-standard-sql

2 Answers

0
votes

Don't use string functions on dates! BigQuery has lots of useful date/time/timestamp functions.

The one you want is extract():

where extract(year from incurred_month_timestamp) = 2013

Or use a range:

where incurred_month_timestamp >= timestamp('2013-01-01') and
      incurred_month_timestamp < timestamp('2014-01-01')
0
votes

A simpler way is using builtin year function in BigQuery Legacy SQL, which extracts the year from a timestamp.

Here is an example with your code.

where year(incurred_month_timestamp) == 2009