Skip to content

generate_dates

generate_dates(start_date, end_date)

Description

Generate a table of dates

Usage

Call or Deploy generate_dates ?
Call generate_dates directly

The easiest way to use bigfunctions

  • generate_dates function is deployed in 39 public datasets for all of the 39 BigQuery regions.
  • It can be called by anyone. Just copy / paste examples below in your BigQuery console. It just works!
  • (You need to use the dataset in the same region as your datasets otherwise you may have a function not found error)

Public BigFunctions Datasets

Region Dataset
eu bigfunctions.eu
us bigfunctions.us
europe-west1 bigfunctions.europe_west1
asia-east1 bigfunctions.asia_east1
... ...
Deploy generate_dates in your project

Why deploy?

  • You may prefer to deploy generate_dates in your own project 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).
  • Get started by reading the framework page

Deployment

generate_dates function can be deployed with:

pip install bigfunctions
bigfun get generate_dates
bigfun deploy generate_dates

Examples

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  |
+------------+-------------+-------------------+-----------------+-------------------+-----------------+-------------+------------+---------------+-------------+

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.

  1. Generate a table of dates covering the period you want to analyze:
SELECT * FROM bigfunctions.us.generate_dates('2023-01-01', '2023-01-31');

  1. Join this generated table with your website traffic data table using the date field as the join key.

  2. 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.

Need help or Found a bug?
Get help using generate_dates

The community can help! Engage the conversation on Slack

We also provide professional suppport.

Report a bug about 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.

We also provide professional suppport.

Show your ❤ by adding a ⭐ on