# Calendar Tables (date spine)

**URL:** <https://community.omni.co/t/calendar-tables-date-spine/252>\
**Category:** Modeling\
**Tags:** modeling\
**Created:** [March 3, 2025, 4:53am UTC](https://community.omni.co/t/calendar-tables-date-spine/252 "2025-03-03T04:53:39Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![russ](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.omni.co/russ/32/296_2.png) [@russ](https://community.omni.co/u/russ)\
**Post date:** [March 3, 2025, 4:53am UTC](https://community.omni.co/t/calendar-tables-date-spine/252/1 "2025-03-03T04:53:39Z")

</div>

You might have heard of a “calendar table” or “date spine”. It’s a technique that starts with a contiguous time series and can be useful in a few scenarios:

- Unifying multiple timestamps on the same common x-axis
- Sparse Data (though you can [fill in missing rows](https://community.omni.co/t/whats-the-difference-between-dimension-fill-and-value-fill/83) in Omni)
- Creating rows between a start date and an end date allowing for various measures and operations

 ![image](https://canada1.discourse-cdn.com/flex004/uploads/omni/original/1X/63cedfa957fed8e4e777a1917b7231f1fabf6289.png)  
_in this example, we are joining opportunities twice to a calendar table, on opened and closed dates, to allow for open pipeline rate analysis, an example of the first useful scenario for calendar tables_

# Snowflake

Using a recursive CTE pattern

#### Create a view:

```yaml
# Reference this view as calendar
sql: |
  WITH RECURSIVE rec_cte AS (
      /* start date */
      SELECT {{ filters.calendar.start_date.range_start }} as DATE
      UNION ALL
      SELECT DATEADD('day',1,DATE) as DATE
      FROM rec_cte
      /* end date (inclusive) */
      WHERE DATE < {{ filters.calendar.end_date.range_end }}
  )
  SELECT DATE FROM rec_cte

dimensions:
  is_ytd:
    sql: |
      EXTRACT(dayofyear FROM ${calendar.date}) <= EXTRACT(dayofyear FROM CURRENT_DATE)
    group_label: Date

  date:
    sql: '"DATE"'
    timeframes:
      [
        year,
        quarter,
        month,
        week,
        date,
        quarter_of_year,
        week_of_year,
        day_of_week_name,
        day_of_week_num,
        month_name,
        month_num,
        day_of_month,
        day_of_year,
        day_of_quarter,
        fiscal_year,
        fiscal_quarter,
        epoch,
        time_of_day
      ]

# optional if you'd like your users to choose the range. Rational to hardcode a wide range instead
filters:
  start_date:
    type: timestamp
    description: Date starting the contiguous range

  end_date:
    type: timestamp
    description: Date ending the contiguous range

```

#### In the topic file (this is the same for all dialects):

This shows how two disparate time series could be compared / unified on the calendar table axis. For a between two effective dates pattern, adjust the `on_sql` parameter to join on all dates between the start and end, this will yield a row for each day in the effective range.

```yaml
# this is the calendar topic
# optional if you allowed the users to select a range, best to fill it with defaults:
default_filters:
  calendar.end_date:
    is: 2029-12-31
  calendar.start_date:
    is: 2020-01-01

joins:
  opened_opportunities: {}
  closed_opportunities: {}

relationships:
#join the same table more than once, but upon different timestamps, giving different meanings to its measures
  - join_from_view: calendar
    join_to_view: opportunity
    join_to_view_as: opened_opportunities
    join_type: always_left
    # joining on opened date
    on_sql: ${calendar.date[date]} = ${opportunity.opened_date[date]}
    relationship_type: one_to_many

  - join_from_view: calendar
    join_to_view: opportunity
    join_to_view_as: closed_opportunities
    join_type: always_left
    # join on closed date
    on_sql: ${calendar.date[date]} = ${opportunity.closed_date[date]}
    relationship_type: one_to_many

```

#### Alternatively, to join between effective dates would look like this in your topic:

```yaml
# this is the calendar_between topic
base_view: calendar

default_filters:
  calendar.end_date:
    is: 2029-12-31
  calendar.start_date:
    is: 2020-01-01

joins:
  open_opportunities: {}

relationships:
  - join_from_view: calendar
    join_to_view: opportunity
    join_to_view_as: open_opportunities
    join_type: always_left
    on_sql: |
      ${calendar.date[date]} >= ${opportunity.opened_date[date]}
      AND ${calendar.date[date]} <= ${opportunity.closed_date[date]}
    relationship_type: one_to_many

```

This allows us a positive row for each day that an opportunity was open, and answers the question “how many opps were open on X day”

 ![image](https://canada1.discourse-cdn.com/flex004/uploads/omni/original/1X/b0c1c3013628186a86315bdf2885581dcba16ace.png)  
And with a count distinct we can easily measure that:  
 ![image](https://canada1.discourse-cdn.com/flex004/uploads/omni/original/1X/e2e4d65b767a20a64c21b0c1c302d9541659b631.png)

# BigQuery

Using a generate / unnest pattern

```yaml
# Reference this view as calendar
sql: |
  SELECT
    date
  FROM
    UNNEST(GENERATE_DATE_ARRAY({{ filters.calendar.start_date.range_start }}, {{ filters.calendar.end_date.range_end }})) AS date

dimensions:
  is_ytd:
    sql: |
      EXTRACT(dayofyear FROM ${calendar.date}) <= EXTRACT(dayofyear FROM CURRENT_DATE)
    group_label: Date

  date:
    sql: '"DATE"'
    timeframes:
      [
        year,
        quarter,
        month,
        week,
        date,
        quarter_of_year,
        week_of_year,
        day_of_week_name,
        day_of_week_num,
        month_name,
        month_num,
        day_of_month,
        day_of_year,
        day_of_quarter,
        fiscal_year,
        fiscal_quarter,
        epoch
      ]

#optional if you'd like your users to be able to select the timeframe. Hardcoding a wide range by changing the above
# variable references to dates is a reasonable design decision
filters:
  start_date:
    type: timestamp
    description: Date starting the contiguous range

  end_date:
    type: timestamp
    description: Date ending the contiguous range

```

# MySQL

Using a recursive CTE pattern

```yaml
# Reference this view as calendar
sql: | 
  WITH RECURSIVE date_range AS (
      SELECT {{ filters.calendar.start_date.range_start }} AS date
      UNION ALL
      SELECT DATE_ADD(date, INTERVAL 1 DAY)
      FROM date_range
      WHERE date < {{ filters.calendar.end_date.range_end }}
  )
  SELECT date from date_range

dimensions:
  is_ytd:
    sql: |
      EXTRACT(dayofyear FROM ${calendar.date}) <= EXTRACT(dayofyear FROM CURRENT_DATE)
    group_label: Date

  date:
    sql: '"DATE"'
    timeframes:
      [
        year,
        quarter,
        month,
        week,
        date,
        quarter_of_year,
        week_of_year,
        day_of_week_name,
        day_of_week_num,
        month_name,
        month_num,
        day_of_month,
        day_of_year,
        day_of_quarter,
        fiscal_year,
        fiscal_quarter,
        epoch
      ]

#optional if you'd like your users to be able to select the timeframe. Hardcoding a wide range by changing the above
# variable references to dates is a reasonable design decision
filters:
  start_date:
    type: timestamp
    description: Date starting the contiguous range

  end_date:
    type: timestamp
    description: Date ending the contiguous range

```

# Postgres

Using a generate series function

```yaml
# Reference this view as calendar
sql: |
    SELECT
        generate_series(
            {{ filters.calendar.start_date.range_start }},
            {{ filters.calendar.end_date.range_end }},
            INTERVAL '1 day'
        )::DATE AS date

dimensions:
  is_ytd:
    sql: |
      EXTRACT(dayofyear FROM ${calendar.date}) <= EXTRACT(dayofyear FROM CURRENT_DATE)
    group_label: Date

  date:
    sql: '"DATE"'
    timeframes:
      [
        year,
        quarter,
        month,
        week,
        date,
        quarter_of_year,
        week_of_year,
        day_of_week_name,
        day_of_week_num,
        month_name,
        month_num,
        day_of_month,
        day_of_year,
        day_of_quarter,
        fiscal_year,
        fiscal_quarter,
        epoch
      ]

#optional if you'd like your users to be able to select the timeframe. Hardcoding a wide range by changing the above
# variable references to dates is a reasonable design decision
filters:
  start_date:
    type: timestamp
    description: Date starting the contiguous range

  end_date:
    type: timestamp
    description: Date ending the contiguous range

```

# What if my dialect isn’t listed?

Connor wrote an excellent article which includes calendar table generation code for more databases, you can find here [link](https://community.omni.co/t/how-do-i-create-a-table-with-a-list-of-dates/49).  
Also AI is excellent at generating these simple calendar table queries, ask ChatGPT, Claude or your favorite foundational model to create a calendar table, but ask it not to use temp tables or stored procedures and you’ll end up with a sql statement like the above, which you can place the optional filter ranges within.