Skip to content

Instantly share code, notes, and snippets.

@KobaKhit
Created February 8, 2024 02:58
Show Gist options
  • Save KobaKhit/266f40c029003524c44412c2f5d527a4 to your computer and use it in GitHub Desktop.
Save KobaKhit/266f40c029003524c44412c2f5d527a4 to your computer and use it in GitHub Desktop.
Examples of generating spines/dates in SQL. Assisted by Caleb Kassa.

Spines in SQL

Given a starting date 2024-02-01 I would like to generate 7 days into the future until February 8th (2024-02-08), ex.g.

dt
2024-02-01
2024-02-02
2024-02-03
2024-02-04
2024-02-05
2024-02-06
2024-02-07
2024-02-08

Snowflake

Using Table Generator

/* Date Spine Example: Specific number of days */
with date_spine as (
    SELECT dateadd(day, '+' || seq4(), '2024-02-01')::DATE as dt
    FROM
      TABLE
        (generator(rowcount => 8)) -- edit to change number of days
)
SELECT dt 
from date_spine;

Using Recursive CTE

/* Date Spine Example: Start and End Dates */
set start_date = '2024-02-01'::DATE;
set end_date = current_date();
WITH RECURSIVE date_spine AS (
    SELECT $start_date as dt
    UNION ALL
    SELECT dateadd(day,1,dt) as dt -- date granularity
    FROM date_spine
    WHERE dt < $end_date -- end date (inclusive)
)
SELECT dt
FROM date_spine;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment