Date Spine

Query

With all_dates as
    (
    SELECT DATEADD(day, ROW_NUMBER() OVER (ORDER BY SEQ4()) - 1,
                   '2025-01-01'::DATE)::DATE AS dt
    FROM TABLE(GENERATOR(ROWCOUNT => 6000))
    )
Select 
    dt,
    CASE
        WHEN {{ time_grain }} = 'Day' THEN TO_CHAR(DATE_TRUNC('day', ad.dt), 'YYYY-MM-DD')
        WHEN {{ time_grain }} = 'Week' THEN TO_CHAR(DATE_TRUNC('week', ad.dt), 'YYYY-MM-DD')
        WHEN {{ time_grain }} = 'Month' THEN TO_CHAR(DATE_TRUNC('month', ad.dt), 'Mon YYYY')
        WHEN {{ time_grain }} = 'Quarter' THEN 'Q' || TO_CHAR(DATE_TRUNC('quarter', ad.dt), 'Q YYYY')
    END AS period_start_display_text,
    CASE
        WHEN {{ time_grain }} = 'Day' THEN DATE_TRUNC('day', ad.dt)
        WHEN {{ time_grain }} = 'Week' THEN DATE_TRUNC('week', ad.dt)
        WHEN {{ time_grain }} = 'Month' THEN DATE_TRUNC('month', ad.dt)
        WHEN {{ time_grain }} = 'Quarter' THEN DATE_TRUNC('quarter', ad.dt)
      END AS period_start_date,
    count(*) as days_in_period

from all_dates ad where dt < CURRENT_DATE
group by ALL

Overview

Generates a continuous sequence of calendar dates starting from 2025-01-01, then groups and labels them according to a configurable time grain. All dates strictly before CURRENT_DATE are included — today is always excluded.

This is the same date spine used in the network growth and health report, reproduced here for the call lengths report.

---

Parameters

Parameter	Accepted Values	Description
{{ time_grain }}	'Day', 'Week', 'Month', 'Quarter'	Controls the aggregation period for display and grouping columns

---

Output Columns

Column	Description
dt	Individual calendar date
period_start_display_text	Human-readable label for the period containing dt (e.g. "2025-01-06", "Jan 2025", "Q1 2025")
period_start_date	Truncated date value for the start of the period — suitable for sorting and joining
days_in_period	Count of daily rows that fall within the same period; useful for prorating or weighting period-level metrics

---

Notes

• At time_grain = 'Day', days_in_period will always be 1 and period_start_date will equal dt.
• period_start_display_text is formatted for display only — use period_start_date for joins and sorting.
• The 6000-row generator covers dates through approximately 2041.