Postgres Generate Date Range

In this comprehensive guide, I’ll cover everything you need to know about generating date ranges in PostgreSQL, from basic techniques to advanced patterns.

Postgres Generate Date Range

PostgreSQL provides a generate_series() function that allows you to create a sequential series of values, including dates and timestamps.

The generate_series() function is incredibly flexible when it comes to generating date sequences. It can create a series of dates by specifying a start date, an end date, and an interval.

Syntax

Here’s the basic syntax:

SELECT generate_series(
    timestamp '2025-05-01',
    timestamp '2025-05-31',
    interval '1 day'
) AS date_series;

This query generates a sequence of timestamps for every day in may 2025. The function takes three parameters:

• Starting timestamp
• Ending timestamp
• Interval between consecutive values

Let’s explore some simple approaches to generating date ranges in PostgreSQL.

Approach 1: Simple Date Series with generate_series()

For a straightforward date series, you can use the following query. This will output the days until May 10th, 2025, which is perfect.

SELECT 
    generate_series::date AS date
FROM generate_series(
    '2025-05-01'::timestamp,
    '2025-05-10'::timestamp,
    '1 day'::interval
);

After executing the above query, I got the expected output as shown in the screenshot below.

Approach 2: Generating Business Days Only

You can use the following query if you need only weekdays for any application.

SELECT 
    day::date AS business_day
FROM generate_series(
    '2025-05-01'::timestamp,
    '2025-05-10'::timestamp,
    '1 day'::interval
) AS day
WHERE EXTRACT(DOW FROM day) NOT IN (0, 6); -- Exclude Sunday (0) and Saturday (6)

After executing the above query, check out the screenshot below, where we got the expected output.

Advanced Date Range Techniques

Now let’s dive into more approaches that showcase the power of PostgreSQL’s date handling capabilities.

Working with Different Time Intervals

PostgreSQL’s generate_series() function allows you to create series with various intervals:

Case-1 Hourly intervals

We can use the following query for this purpose.

SELECT generate_series(
    '2025-07-04 00:00:00'::timestamp,
    '2025-07-04 23:59:59'::timestamp,
    '1 hour'::interval
) AS hourly_series;

After executing the above query, I got the expected output as shown in the screenshot below.

Case-2 15-minute intervals

For 15-minute intervals, we can use the below query.

-- 15-minute intervals
SELECT generate_series(
    '2025-07-04 09:00:00'::timestamp,
    '2025-07-04 17:00:00'::timestamp,
    '15 minutes'::interval
) AS quarter_hour_series;

Case-3 Monthly intervals

We can use the below query for the monthly intervals.

-- Monthly intervals
SELECT generate_series(
    '2025-01-01'::timestamp,
    '2025-12-31'::timestamp,
    '1 month'::interval
) AS monthly_series;

After executing the above query, I got the expected output as shown in the below screenshot.

Using the DateRange Data Type

PostgreSQL offers a specialized daterange data type that provides even more functionality for working with date ranges.

Introduction to DateRange

The DateRange data type in PostgreSQL allows you to store and manage periods of time by keeping both start and end dates in a single column. This approach offers several advantages:

• More efficient storage
• Built-in range operations
• Simplified queries for overlapping dates
• Constraint enforcement

Creating Tables with DateRange

Here’s how to create a table using the daterange type:

CREATE TABLE project_schedules (
    project_id SERIAL PRIMARY KEY,
    project_name VARCHAR(100),
    schedule daterange,
    EXCLUDE USING gist (schedule WITH &&) -- Prevents overlapping schedules
);

After executing the above query, I got the expected output and the table created successfully as shown in the below screenshot.

Inserting DateRange Data

INSERT INTO project_schedules (project_name, schedule)
VALUES 
    ('Website Redesign', daterange('2023-06-01', '2023-07-15', '[]')),
    ('Mobile App Development', daterange('2023-07-16', '2023-09-30', '[]')),
    ('Year-End Marketing Campaign', daterange('2023-10-01', '2023-12-15', '[]'));

After executing the above query, the data inserted successfully as shown in the below screenshot.

Conclusion

We can use the generate_series() function to achieve the PostgreSQL’s date range capabilities. You can use the approaches and best practices mentioned in this article to implement this functionality in your project. Thanks for reading this article !!!

You may also like following the articles below.

• PostgreSQL current_timestamp
• PostgreSQL Order By Date
• PostgreSQL Date Part Hour