Date Spine

Query

--Super simple query for generating a date spine with user-input reporting periods in Hex

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 
    ad.dt,

    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

from all_dates ad where dt < CURRENT_DATE

Overview

Generates a continuous sequence of calendar dates starting from 2025-01-01, assigning each date to a period start via DATE_TRUNC at the selected grain. All dates strictly before CURRENT_DATE are included — today is always excluded.

A simpler variant of the network growth date spine — this version omits the display label and days_in_period count, outputting only dt and period_start_date.

---

Parameters

Parameter	Accepted Values	Description
{{ time_grain }}	'Day', 'Week', 'Month', 'Quarter'	Controls how each date is mapped to a period start

---

Output Columns

Column	Description
dt	Individual calendar date
period_start_date	Start of the period containing dt, truncated to the selected grain

---

Notes

• At time_grain = 'Day', period_start_date will always equal dt.
• The 6000-row generator covers dates through approximately 2041.