How do I create a table with a list of dates?

Patterns
1

It’s often helpful to have a comprehensive list of dates to support time-based reporting and analysis:

date
2020-01-01
2020-01-02
2020-01-03
2020-01-04
2020-01-05
etc.

A common example is analyzing subscriptions: you have a table of subscriptions with a start_date and end_date, and you want to see the number of active subscriptions per day. To do so, you can join your subscriptions table onto your dates table where the date falls between the start_date and end_date.

Thankfully, it’s easy to create a Dates table. The syntax and method varies slightly by data warehouse, so we’ve included example code snippets specific to a few warehouses below, but the general logic will be the same across databases.

You can save these code snippets as a query view in your shared model and then reference it in any workbook. And once you have a date table, Omni makes it easy to query for different time frames (day of month, day of quarter, month name, etc.).

Here’s an example of this workflow in its entirety:

Snowflake

  SELECT
      DATEADD('day', seq4(), '2020-01-01') as date
  FROM TABLE(GENERATOR(rowcount => 366 * 5) )

This code works by creating a blank table with X number of rows (TABLE(GENERATOR(rowcount => 366 * 5)), numbering each of those rows starting from 0 (seq4()), and then incrementing a starting date by that number to generate a list of sequential dates.

  • To change the starting date: Replace ‘2020-01-01’ with your desired starting date
  • To change the number of dates in your table: If you know the exact number of days you’d like in your table, replace ‘366 * 5’ with the number of days you’d like. If you’d like to input a certain number of years, you can change the ‘5’ after the ‘366 *’ to alter the number of years.
    • Note: You can always filter the date table in Omni to only include certain dates, so it’s best to overestimate the number of dates here and then filter down in a workbook.

BigQuery

  SELECT *
  FROM UNNEST(GENERATE_DATE_ARRAY('2020-01-01', '2020-02-01', INTERVAL 1 DAY)) AS date

This code works by generating a date array from a given start date, end date, and interval length (GENERATE_DATE_ARRAY('2014-01-01', '2050-01-01', INTERVAL 1 DAY)), and then unnest()ing that array to transform it into a table.

To change the start date, end date, or interval length, simply update the arguments in the GENERATE_DATE_ARRAY() function.

Redshift

SELECT
    DATE(DATEADD('day', enum.n, DATE('2020-01-01'))) AS date
FROM
(
    SELECT
        ROW_NUMBER() OVER() - 1 AS n
    FROM order_items -- example table
    LIMIT 365
) AS enum
WHERE
    date BETWEEN '2020-01-01' and CURRENT_DATE

To create this table in Redshift, you’ll need to identify a table with at least as many rows as the number of days you’d like in your table. For example, if you want a dates table with 3 years’ worth of dates, you’ll need to identify a table with at least 365*3 = 1095 rows.

With that table identified, you can substitute order_items (our example table) with the name of your table. In addition…

  • To change the starting date: Replace ‘2020-01-01’ with your desired starting date
  • To change the number of dates in your table: If you know the exact number of days you’d like in your table, replace the LIMIT 365 with a LIMIT of the number of rows you’d like. Alternatively, you can change the WHERE clause to filter for a specific date range.
    • Note: You can always filter the date table in Omni to only include certain dates, so it’s best to overestimate the number of dates here and then filter down in a workbook.

PostgreSQL

SELECT '2020-01-01'::DATE + SEQUENCE.DAY as date
FROM GENERATE_SERIES(0, 366*5) AS SEQUENCE (DAY)

This code works by generating a column of sequenced numbers from 0 to 366*5, then incrementing a start date by each of those numbers to generate a sequenced list of dates.

  • To change the starting date: Replace ‘2020-01-01’ with your desired starting date
  • To change the number of dates in your table: If you know the exact number of days you’d like in your table, replace ‘366 * 5’ with the number of days you’d like. If you’d like to input a certain number of years, you can change the ‘5’ after the ‘366 *’ to alter the number of years.
    • Note: You can always filter the date table in Omni to only include certain dates, so it’s best to overestimate the number of dates here and then filter down in a workbook.

Databricks

SELECT EXPLODE(SEQUENCE(DATE '2020-01-01', DATE '2020-02-01', INTERVAL 1 DAY)) as date

This code works by generating a date array from a given start date, end date, and interval length (SEQUENCE(DATE '2020-01-01', DATE '2020-02-01', INTERVAL 1 DAY)), and then explode()ing that array into a table.

To change the start date, end date, or interval length, simply update the arguments in the SEQUENCE() function.