How to Generate Date Series in PostgreSQL: Simple Guide

In PostgreSQL, you can generate a series of dates using the generate_series() function by specifying a start date, an end date, and an interval like '1 day'. This function returns a set of dates incremented by the interval between the start and end dates.

Syntax

The generate_series() function creates a sequence of values. For dates, it requires three parts:

• start_date: The first date in the series.
• end_date: The last date in the series.
• interval: The step size between dates, such as '1 day', '1 week', or '1 month'.

The function returns each date from start to end, stepping by the interval.

sql

SELECT generate_series(start_date, end_date, interval) AS date_series;

Example

This example generates all dates from January 1, 2024, to January 5, 2024, stepping one day at a time. It shows how to use generate_series() with dates and intervals.

sql

SELECT generate_series('2024-01-01'::date, '2024-01-05'::date, '1 day'::interval) AS date_series;

Output

 date_series 
-------------
 2024-01-01
 2024-01-02
 2024-01-03
 2024-01-04
 2024-01-05
(5 rows)

Common Pitfalls

Some common mistakes when generating date series include:

• Forgetting to cast strings to date or timestamp types, which can cause errors.
• Using incorrect interval formats like '1days' instead of '1 day'.
• Expecting the function to include the end date when the interval does not evenly divide the range.

Always cast your inputs properly and use valid interval strings.

sql

/* Wrong: Missing type cast and wrong interval format */
-- SELECT generate_series('2024-01-01', '2024-01-05', '1days');

/* Correct: Proper casts and interval format */
SELECT generate_series('2024-01-01'::date, '2024-01-05'::date, '1 day'::interval);

Quick Reference

Parameter	Description	Example
start_date	The first date in the series	'2024-01-01'::date
end_date	The last date in the series	'2024-01-31'::date
interval	Step size between dates	'1 day'::interval
Return	Set of dates from start to end	2024-01-01, 2024-01-02, ...

Key Takeaways

Use generate_series(start_date, end_date, interval) to create date sequences in PostgreSQL.

Always cast string literals to date or timestamp types to avoid errors.

Specify the interval clearly, like '1 day' or '1 week', for correct stepping.

The function includes the end date if it fits exactly in the interval steps.

Common errors come from missing casts or incorrect interval strings.