generate_dates¶

Call or Deploy generate_dates ?

✅ You can call this generate_dates bigfunction directly from your Google Cloud Project (no install required).

This generate_dates function is deployed in bigfunctions GCP project in 39 datasets for all of the 39 BigQuery regions. You need to use the dataset in the same region as your datasets (otherwise you may have a function not found error).
Function is public, so it can be called by anyone. Just copy / paste examples below in your BigQuery console. It just works!
You may prefer to deploy the BigFunction in your own project if you want to build and manage your own catalog of functions. This is particularly useful if you want to create private functions (for example calling your internal APIs). Discover the framework

Public BigFunctions Datasets:

Region	Dataset
`eu`	`bigfunctions.eu`
`us`	`bigfunctions.us`
`europe-west1`	`bigfunctions.europe_west1`
`asia-east1`	`bigfunctions.asia_east1`
...	...

Description¶

Signature

generate_dates(start_date, end_date)

Description

Generate a table of dates

Examples¶

EUUSeurope-west1

select * from bigfunctions.eu.generate_dates(date('2023-01-01'), date('2023-01-05'))

select * from bigfunctions.us.generate_dates(date('2023-01-01'), date('2023-01-05'))

select * from bigfunctions.europe_west1.generate_dates(date('2023-01-01'), date('2023-01-05'))


+------------+-------------+-------------------+-----------------+-------------------+-----------------+-------------+------------+---------------+-------------+
|    date    | day_of_week | week_start_monday | week_end_monday | week_start_sunday | week_end_sunday | month_start | month_end  | quarter_start | quarter_end |
+------------+-------------+-------------------+-----------------+-------------------+-----------------+-------------+------------+---------------+-------------+
| 2023-01-01 |     Sun     |    2022-12-26     |    2023-01-01   |     2023-01-01    |    2023-01-07   |  2023-01-01 | 2023-01-31 |   2023-01-01  | 2023-03-31  |
| 2023-01-02 |     Mon     |    2023-01-02     |    2023-01-08   |     2023-01-01    |    2023-01-07   |  2023-01-01 | 2023-01-31 |   2023-01-01  | 2023-03-31  |
| 2023-01-03 |     Tue     |    2023-01-02     |    2023-01-08   |     2023-01-01    |    2023-01-07   |  2023-01-01 | 2023-01-31 |   2023-01-01  | 2023-03-31  |
| 2023-01-04 |     Wed     |    2023-01-02     |    2023-01-08   |     2023-01-01    |    2023-01-07   |  2023-01-01 | 2023-01-31 |   2023-01-01  | 2023-03-31  |
| 2023-01-05 |     Thu     |    2023-01-02     |    2023-01-08   |     2023-01-01    |    2023-01-07   |  2023-01-01 | 2023-01-31 |   2023-01-01  | 2023-03-31  |
+------------+-------------+-------------------+-----------------+-------------------+-----------------+-------------+------------+---------------+-------------+

Need help using generate_dates?

The community can help! Engage the conversation on Slack

For professional suppport, don't hesitate to chat with us.

Found a bug using generate_dates?

If the function does not work as expected, please

report a bug so that it can be improved.
or open the discussion with the community on Slack.

For professional suppport, don't hesitate to chat with us.

Use cases¶

The generate_dates function is useful for several scenarios requiring a series of dates, including:

Generating time series data: If you need to analyze trends or patterns over time but only have data for certain dates, you could use generate_dates to create a complete date range and then join it with your existing data, filling in missing values as needed. For example, you might track daily website visits but have some gaps in your data. This function can help create a continuous date series.
Creating date dimension tables for a data warehouse: A date dimension table is a common component of data warehouses. It stores a comprehensive list of dates along with related attributes like day of week, week start/end dates, month start/end dates, quarter start/end dates, etc. generate_dates provides many of these attributes, facilitating the creation of such a table.
Scheduling or automating tasks: You could use generate_dates to create a list of dates for a specific period, then use that list to schedule tasks or trigger automated processes. For example, you could generate a list of dates representing business days in the next month and then schedule a report to be generated on each of those dates.
Backfilling or forecasting data: If you need to backfill missing data or generate forecasts for future periods, generate_dates can help provide the date framework. For backfilling, you'd specify a past date range, and for forecasting, you'd specify a future date range.
Simplifying date calculations in queries: Instead of performing complex date calculations within a query, you can pre-calculate these values using generate_dates and store them in a table. This can make your queries simpler, easier to understand, and potentially more efficient. For instance, determining the start of the week for various dates becomes a simple lookup rather than a calculation in each query.

Example: Analyzing website traffic trends even with missing data points for specific dates.

Generate a table of dates covering the period you want to analyze:

SELECT * FROM bigfunctions.us.generate_dates('2023-01-01', '2023-01-31');

Join this generated table with your website traffic data table using the date field as the join key.
You now have a row for each date, even if your original website traffic data was missing entries for some dates. You can then use functions like COALESCE or IFNULL to fill in missing traffic values with zeros or other appropriate placeholders. This enables continuous trend analysis without being affected by missing data points.

Spread the word¶

BigFunctions is fully open-source. Help make it a success by spreading the word!

Share on Add a on