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 in Omni)
- Creating rows between a start date and an end date allowing for various measures and operations
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:
# 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.
# 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:
# 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”
And with a count distinct we can easily measure that:
BigQuery
Using a generate / unnest pattern
# 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
# 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
# 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.
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.