Monthly Date Series — generate_series in SQL

The problem

Brightlane's financial reporting system generates the start date for each of the six billing periods in the first half of 2024. Each period begins on the first day of a calendar month, from January through June.

Write a query to return the first day of each calendar month from January through June 2024, one date per row.

Output:

Six rows, with one column, period_start, typed as a calendar date and holding the dates '2024-01-01', '2024-02-01', '2024-03-01', '2024-04-01', '2024-05-01', and '2024-06-01'.

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-01'::date, '2024-06-01'::date, INTERVAL '1 month')::date AS period_start

The shape

generate_series with a monthly interval steps from January 1 to June 1, emitting the first of each month along the way. The ::date cast at the end converts the function's timestamp output back to a plain date so the column type matches what the finance report expects.

Clause by clause

generate_series('2024-01-01'::date, '2024-06-01'::date, interval '1 month') starts at January 1, walks forward one calendar month at a time, and stops once the next candidate would exceed June 1. The function emits exactly six values: the first of each month from January through June.
The trailing ::date cast wraps the function output. When the inputs are dates and the step is an interval, generate_series returns timestamp values; the cast drops the time component and returns each value as a calendar date, which is the type the billing periods need.
AS period_start labels the output column so each row reads as a billing-period start.

The trap

Forgetting the ::date cast on the function output leaves the column typed as timestamp, even though every value's time component is midnight. That looks identical on screen but breaks any downstream join against a date column, because the types do not line up. Cast the result back to date whenever the inputs are dates and the step is an interval. The cast is what keeps the type honest.

You practiced generate_series(start, end, interval '1 month')::date — calendar-month sequence with explicit cast back to DATE after the function returns timestamps.

Return the first day of each calendar month from January through June `2024`, one date per row

The shape

Clause by clause

The trap

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