Using Periscope's Date Range Filter to Dynamically Update BigQuery Results

BigQuery, unlike other flavors of SQL, breaks out its tables by day. Each table has a suffix with the date included, but what if you want to allow your end-user to dynamically query only tables from certain dates?

By using table wildcard operators in conjunction with Periscope Data's built-in Date Range filter, this is definitely possible!

For StandardSQL

select
  *
from
 `table_prefix_*`
where
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', [daterange_start]) AND FORMAT_DATE('%Y%m%d', [daterange_end])

For LegacySQL

#legacySQL
SELECT
  *
FROM
  TABLE_DATE_RANGE([project:dataset.tableprefix.table_prefix], TIMESTAMP([daterange_start]), TIMESTAMP([daterange_end]))
Reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
Like4 Follow
  • 4 Likes
  • 1 yr agoLast active
  • 938Views
  • 3 Following