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

**URL:** <https://community.omni.co/t/how-do-i-create-a-table-with-a-list-of-dates/49>\
**Category:** Patterns\
**Created:** [April 30, 2024, 12:53am UTC](https://community.omni.co/t/how-do-i-create-a-table-with-a-list-of-dates/49 "2024-04-30T00:53:45Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![conner](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.omni.co/conner/32/11_2.png) [@conner](https://community.omni.co/u/conner)\
**Post date:** [April 30, 2024, 12:53am UTC](https://community.omni.co/t/how-do-i-create-a-table-with-a-list-of-dates/49/1 "2024-04-30T00:53:45Z")

</div>

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](https://docs.omni.co/querying-and-sql/saved-views) 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

```auto
  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

```auto
  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

```auto
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

```auto
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.