Acquire / generate · All operations

Generate a range of dates

A variant of Generate rows

Emit one row per date or timestamp between a start and an end at a chosen grain.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Emit a row for each day, week, month and hour between two bounds

It reads the orders table the proofs share. Create it first:

SQL

CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL

CREATE TEMP TABLE spans AS
SELECT (SELECT list(d::DATE ORDER BY d) FROM generate_series(DATE '2026-01-01', DATE '2026-01-04', INTERVAL 1 DAY) t(d)) AS days,
       (SELECT list(d::DATE ORDER BY d) FROM generate_series(DATE '2026-01-05', DATE '2026-01-26', INTERVAL 1 WEEK) t(d)) AS weeks,
       (SELECT list(d::DATE ORDER BY d) FROM generate_series(DATE '2026-01-01', DATE '2026-04-01', INTERVAL 1 MONTH) t(d)) AS months,
       (SELECT list(d ORDER BY d) FROM generate_series(TIMESTAMP '2026-01-01 22:00', TIMESTAMP '2026-01-02 01:00', INTERVAL 1 HOUR) t(d)) AS hours;
-- a spine joined to the orders keeps the days that have none
CREATE TEMP TABLE result AS
SELECT s.day, count(o.order_id) AS orders
FROM (SELECT d::DATE AS day FROM generate_series(DATE '2026-01-03', DATE '2026-01-06', INTERVAL 1 DAY) t(d)) s
LEFT JOIN orders o ON o.order_date = s.day GROUP BY s.day;
SELECT * FROM spans; SELECT * FROM result ORDER BY day;
SELECT CASE WHEN ((SELECT len(days) = 4 AND len(weeks) = 4 AND weeks[4] = DATE '2026-01-26' AND months = [DATE '2026-01-01', DATE '2026-02-01', DATE '2026-03-01', DATE '2026-04-01'] AND len(hours) = 4 AND hours[4] = TIMESTAMP '2026-01-02 01:00' FROM spans) AND (SELECT list(orders ORDER BY day) FROM result) = [1, 0, 2, 0]) THEN 'as expected' ELSE error('the spine did not give 4 days, 4 weeks, 4 months and 4 hours, or keep the days with no order') END AS verdict;

It needs nothing installed beyond DuckDB itself.
The statement checks its own result and raises an error when it is wrong: “the spine did not give 4 days, 4 weeks, 4 months and 4 hours, or keep the days with no order”.
It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Bruin (1)

bruin.date_spine(datepart, start_date, end_date)read 2026-09-27
Emit a one-column date or timestamp spine at a datepart grain

dbt-utils (1)

date_spineread 2026-09-27
Generate one row per datepart between a start date and an exclusive end date

EasyMorph (1)

Calendarread 2026-09-27
Generate one row per date across a date range

Ibis (1)

TimestampRangeread 2026-09-27
Generate an array of timestamps from start to stop by an interval step

KNIME Analytics Platform (1)

Date&Time Range Creatorread 2026-09-27
Generate a sequence of date&time values from start, end, interval or row count

SQLMesh (1)

@DATE_SPINEread 2026-09-27
Generate one row per datepart between a start date and an inclusive end date

Background

In DuckDB, generate_series builds a table of timestamps over the closed range between two bounds, stepping by an interval.^[1] SQLMesh's @DATE_SPINE macro returns the SQL for a date spine between a start date and an end date, at a datepart of day, week, month, quarter or year.^[2]

When to use it

SQLMesh's @DATE_SPINE is typically used to join fixed date ranges to other tables, so that date ranges need not be adjusted in WHERE clauses across many SQL models.^[2] Build the range from the first to the last day a report covers, not from the dates the data happens to hold, so that a day with nothing in it still gets a row. (Meridian's view) KNIME's Date&Time Range Creator node is typically used to generate time series data for simulations, forecasts or testing.^[3]

In the dbt-utils package, the date_spine macro includes the start date if it is aligned to the datepart, but does not include the end date.^[4] SQLMesh's @DATE_SPINE macro includes the end date, unlike the date_spine macro in dbt-utils.^[2] In KNIME's Date&Time Range Creator, the end date is inclusive, except in the Number and Duration mode when the interval does not divide evenly between the start and end.^[3] Check whether the tool includes the end of a range before you set it, and set the end to the last day you want or the day after it to match. (Meridian's view)

In PostgreSQL, the time-zone-aware generate_series computes times of day and daylight-savings adjustments in the time zone its timezone argument names, or in the current TimeZone setting if that argument is omitted.^[5] Step a daily range of zoned timestamps in the zone the business keeps its days in, or generate plain dates, so that a clock change cannot shift where a day starts. (Meridian's view)

A calendar date dimension in a data warehouse represents dates with a granularity of a day, and can carry attributes such as the week of the year and flags for work days and holidays.^[6] One reason to have date dimensions is to place calendar knowledge in the data warehouse, since a simple SQL date-timestamp cannot give information about holidays or fiscal periods.^[6] A date dimension should be initialised with all the required dates, such as the next 10 years of dates, or past dates if past events are handled.^[6] A time dimension with a grain of seconds in a day has only 86400 rows.^[6]

The method

In DuckDB, generate_series and range accept TIMESTAMP and TIMESTAMP WITH TIME ZONE values, and for these types the stop and step arguments must be given explicitly.^[7] In DuckDB, generate_series from DATE '1992-01-01' to DATE '1992-03-01' by INTERVAL '1' MONTH returns three values, each with the time 00:00:00.^[7] Cast the values back to DATE when you want a column of days rather than a column of midnights. (Meridian's view) In DuckDB, infinite values are not allowed as the bounds of the timestamp table functions.^[1]

In DuckDB SQL, SQLMesh renders @date_spine('day', '2024-01-01', '2024-01-16') as a GENERATE_SERIES between the two dates, cast to dates and unnested into a column named date_day.^[2] On Redshift and MySQL, SQLMesh's @DATE_SPINE macro builds the spine with recursive common table expressions (CTEs).^[2] In PostgreSQL, a daily series of zoned timestamps stepped in America/New_York and shown in UTC moves from 04:00 to 05:00 at the daylight-saving change of late October 2001.^[5] EasyMorph's Calendar action creates one row per day for a date range, and its start and end can be absolute dates, relative to today, or set by a parameter.^[8]

Compared with the alternatives

A calendar date dimension's primary key could be a surrogate key or a number in the format YYYYMMDD.^[6] A generated range of dates is the bare column a date dimension starts from: add the week, the fiscal period and the holiday flags as columns on top of it, and keep it as a table when several reports share it. (Meridian's view)

Sources

[1]Timestamp Functions – DuckDBdocumentation
[2]sqlmesh/docs/concepts/macros/sqlmesh_macros.md at 23b237cea7218cc8f338b89c14f0957394a0003b · SQLMesh/sqlmesh · GitHubdocumentation
[3]Date&Time Range Creatordocumentation
[4]dbt-utils README.mddocumentation
[5]PostgreSQL: Documentation: 18: 9.26. Set Returning Functionsdocumentation
[6]Dimension (data warehouse) - Wikipediasecondary source
[7]List Functions – DuckDBdocumentation
[8]CALENDAR [EasyMorph Help]documentation