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
order by 1

Overview

Generates a continuous sequence of calendar dates starting from 2025-01-01, then groups and labels them according to a configurable time grain. Used as the date spine for time-series reports.

All dates strictly before CURRENT_DATE are included — today is always excluded.

---

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 (the raw daily grain, always present regardless of time_grain)
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

---

Logic

all_dates CTE

Uses TABLE(GENERATOR(ROWCOUNT => 6000)) with ROW_NUMBER() OVER (ORDER BY SEQ4()) to produce ~16 years of sequential dates from 2025-01-01. The result is filtered to dt < CURRENT_DATE before output.

Period grouping

Each dt is assigned to a period via DATE_TRUNC keyed on {{ time_grain }}. The GROUP BY ALL collapses individual dates into their period buckets, and days_in_period reflects how many calendar days fall in each bucket up to (but not including) today.

---

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 and should not be used for date arithmetic — use period_start_date for joins and sorting.
• The 6000-row generator covers dates through approximately 2041; no changes needed for the foreseeable future.