How to extract unique days between two timestamps in BigQuery?
You can do this with a cross join on public dataset ( fh-bigquery:geocode.numbers_65536
) where you have numbers up to: 65536
SELECT date(DATE_ADD(DAY, i, "DAY")) DAY
FROM
(SELECT '2015-01-01' AS DAY) a CROSS
JOIN
(SELECT i
FROM [fh-bigquery:geocode.numbers_65536]
WHERE i<=abs(DATEDIFF('2015-01-01','2015-01-15'))) b
ORDER BY DAY ASC
these outputs:
+-----+------------+---+
| Row | day | |
+-----+------------+---+
| 1 | 2015-01-01 | |
| 2 | 2015-01-02 | |
| 3 | 2015-01-03 | |
| 4 | 2015-01-04 | |
| 5 | 2015-01-05 | |
| 6 | 2015-01-06 | |
| 7 | 2015-01-07 | |
| 8 | 2015-01-08 | |
| 9 | 2015-01-09 | |
| 10 | 2015-01-10 | |
| 11 | 2015-01-11 | |
| 12 | 2015-01-12 | |
| 13 | 2015-01-13 | |
| 14 | 2015-01-14 | |
| 15 | 2015-01-15 | |
+-----+------------+---+
You can add this data to your view in the BigQuery UI by adding a project fh-bigquery
using the project menu (drop-down menu next to the project name, Switch to Project, Display Project). Alternatively, you can go to the BigQuery interface link https://bigquery.cloud.google.com/project/fh-bigquery
After adding a project, a sample dataset (fh-bigquery) appears in the navigation bar.
source to share
@ Pentium10's answer is the correct and classic way to fill in ranges. Just for fun, I wanted to also provide an alternative that doesn't rely on any additional table. This solution looks like this:
- Use RPAD to generate a string of the required length, i.e. number of days in the interval
- Use SPLIT to convert it to a repeating field with cardinality equal to the number of days in the interval
- Use POSITION to get the sequential index of each item in a repeating field
Here's the whole query:
select date(date_add(day, i, "DAY")) day
from (select '2015-01-01' day) a
cross join
(select
position(
split(
rpad('', datediff('2015-01-15','2015-01-01')*2, 'a,'))) i
from (select NULL)) b;
source to share