CODEWITHSUNDEEP
google-analyticsgoogle-bigqueryfirebase-analytics
SMGhost
SMGhost

Reputation: 4037

BigQuery Analytics current day query

I'm trying to write a query that could be executed every day to get up to date statistics for my analytic events. Unfortunately my table path looks like this:

appname-72a9c.analytics_211719331.events_20210418

I can easily query data by providing hardcoded string, but is there a way to dinamically specify the date?

I tried writing something like this:

SELECT
  e.param1, e.param2
FROM
  FORMAT_DATETIME("appname-72a9c.analytics_211719331.events_%Y%m%d", CURRENT_DATE()) AS e

But it doesn't work.

Upvotes: 0

Views: 34

Answers (1)

Ben P
Ben P

Reputation: 3369

You can parse the date string piece of your table name like this:

FROM `my_project.my_dataset.events_*`
WHERE parse_date('%Y%m%d', _table_suffix) = CURRENT_DATE() -1

This is a way to hardcode a query for yesterday, which you could customise as required.

Upvotes: 2

Related Questions