BigQuery - Select partition key when reading across several tables

Question

My tables are partitioned by day, like table_20180501, table_20180502, ... They don't have a date column since it's implicit with the partitioning. I'd like to select across several tables and get the date in my selection.

Something like

SELECT
    _TABLE_SUFFIX as date,
    *
FROM (TABLE_DATE_RANGE(
    [table_],
    TIMESTAMP(DATE_ADD(TIMESTAMP(CONCAT(CURRENT_DATE(), ' 00:00:00')), -39, 'DAY')),
    TIMESTAMP(DATE_ADD(DATE_ADD(DATE_ADD(TIMESTAMP(CONCAT(CURRENT_DATE(), ' 00:00:00')), -39, 'DAY'), 40, 'DAY'),-1, 'SECOND'))
))

That doesn't work (Field '_TABLE_SUFFIX' not found)

Answer 1

Below are examples for BigQuery Standard SQL

#standardSQL
SELECT
  _TABLE_SUFFIX AS dt,
  COUNT(1) cnt
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE _TABLE_SUFFIX BETWEEN '20170718' AND '20170719'
GROUP BY dt   


#standardSQL
SELECT
  _TABLE_SUFFIX AS dt,
  COUNT(1) cnt
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 318 DAY)) 
  AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 317 DAY))
GROUP BY dt  

both examples will return something like

Row dt          cnt  
1   20170717    2923     
2   20170718    3072     

Hope this gives you right direction