CODEWITHSUNDEEP
wildcardgoogle-bigquery
Zzema
Zzema

Reputation: 307

How to pull data from the past 3 days, not including today's and yesterday's from Bigquery

Using standars SQL. When the table consists of wildcard tables. I Try to use this:

SELECT * 
FROM `Resourse.Reports_Reg.Session_streaming`
WHERE
      SUBSTR(_table_suffix, 0, 6) =
        FORMAT_DATE("%E4Y%m", DATE_SUB(CURRENT_DATE, INTERVAL 3 day))

This request, I thought, should return data for the last 5 days. But I still do not understand what data it returns to me. How to pull data from the past 3 days, not including today's and yesterday's

Upvotes: 0

Views: 387

Answers (1)

Willian Fuks
Willian Fuks

Reputation: 11777

As it is, you are not selecting anything into _table_suffix given that your query has no wildcard.

If your table have for instance a name structure like:

`Resourse.Reports_Reg.Session_streaming_20170820`

where the very last string is a date with format "%Y%m%d", then selecting past third and second days can be done like:

SELECT 
  * 
FROM `Resourse.Reports_Reg.Session_streaming_*`
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE, INTERVAL 3 day)) AND FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE, INTERVAL 2 day))

Notice the wildcard "*" is selecting the date "20170820" for instance. After that, there's just a where clause selecting appropriate dates.

Upvotes: 1

Related Questions