Database Reference
In-Depth Information
$
bq query "SELECT kind, MIN(day), MAX(day)
FROM (TABLE_DATE_RANGE(ch11.a_
,
DATE_ADD(TIMESTAMP('20131114'), -3
,
'DAY')
,
CURRENT_TIMESTAMP()))
GROUP BY 1"
+------+----------+----------+
| kind | f0_ | f1_ |
+------+----------+----------+
| a | 20131111 | 20131114 |
+------+----------+----------+
Notice that for start and end timestamp you can supply any expression that
evaluates to a timestamp. To use date constants you can cast a string to a
timestamp:
$
bq query "SELECT kind, MIN(day), MAX(day)
FROM (TABLE_DATE_RANGE(ch11.b_
,
TIMESTAMP('2013-11-09')
,
TIMESTAMP('2013-11-11')))
GROUP BY 1"
+------+----------+----------+
| kind | f0_ | f1_ |
+------+----------+----------+
| b | 20131109 | 20131111 |
+------+----------+----------+
The reason this function is particularly helpful is that it can be a chore to
work out the conditions that select out an arbitrary date range. It is certainly
possible to implement the same functionality using
TABLE_QUERY
directly,
but this function makes it a lot simpler and avoids common date handling
errors.
This completes the discussion of metatables in BigQuery. The reason these
one-row tables (or appends to a single table) were created was to illustrate