Daily Date Series — generate_series in SQL

The problem

Brightlane's workforce scheduling system generates one record for each day of a work week.

Write a query to return a sequence covering every day from '2024-01-08' through '2024-01-14', one date per row.

Output:

Seven rows, with one column, shift_date, typed as a calendar date and holding the seven consecutive dates from '2024-01-08' through '2024-01-14'.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  GENERATE_SERIES('2024-01-08'::date, '2024-01-14'::date, INTERVAL '1 day')::date AS shift_date

The shape

generate_series with a daily step over the seven-day window emits one row for each calendar day from January 8 through January 14. The ::date cast on the result drops the timestamp component the function returns, leaving a plain calendar date per row.

Clause by clause

generate_series('2024-01-08'::date, '2024-01-14'::date, interval '1 day') walks one day at a time from the start date to the end date and emits seven values. The function expands into seven rows in place, which is why the query has no FROM.
The trailing ::date cast converts each value back to a calendar date. With date inputs and an interval step, generate_series returns timestamp, and the cast is what keeps the output column typed the way the shift schedule expects.
AS shift_date labels the output column. Each row now reads as a single shift date rather than the raw function name.

Why this and not seven hard-coded date literals

A literal list of seven SELECT '2024-01-08'::date UNION ALL ... clauses produces the same rows on this exact problem, but it does not scale. generate_series takes the range as arguments, so widening the window to a full month is a two-character edit on the end date. The function is also the standard seed for the date-spine pattern; even on a seven-row problem, recognising the shape is what makes the larger pattern feel natural later.

You practiced generate_series with a daily step over a date range — the canonical date-spine seed for seven-day analyses.

Return a sequence covering every day from `'2024-01-08'` through `'2024-01-14'`, one date per row

The shape

Clause by clause

Why this and not seven hard-coded date literals

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.