Using TABLE_DATE_RANGE with tables over 1 year old

Question

I am trying to use Google bigquery to select data from date wildcards. I would like to be able to use the TABLE_DATE_RANGE function, but I need to request a large date range (> 1 year). Right now my request works for a year, and the data that I receive:

Error: error TABLE_DATE_RANGE: too many days

#Fails SELECT system_id, sample_date, e_pv_array FROM (TABLE_DATE_RANGE(workspace.intervaldata, TIMESTAMP('2009-03-01'), TIMESTAMP('2010-03-04'))) WHERE system_id = 20006 and e_pv_array is not null; 

---

 #Works SELECT system_id, sample_date, e_pv_array FROM (TABLE_DATE_RANGE(workspace.intervaldata, TIMESTAMP('2009-03-01'), TIMESTAMP('2010-03-03'))) WHERE system_id = 20006 and e_pv_array is not null; 

Is this just a bigquery restriction? Or is there a way to use table lookups with date ranges over 1 year?

Answer 1

I am also having problems with TABLE_DATE_RANGE (). Here's a workaround possible, assuming your tables are called workspace.intervaldata20090303, etc .:

 SELECT system_id, sample_date, e_pv_array FROM TABLE_QUERY(workspace, "integer(regexp_extract(table_id, r'intervaldata([0-9]+)')) BETWEEN 20090301 and 20100304") WHERE system_id = 20006 and e_pv_array is not null; 

I have a similar scenario (tables by date) and I can easily run queries with data for 2 years using TABLE_QUERY ().