SQL Generate Date: How to Generate Dates in PostgreSQL

Introduction

When working with SQL databases, it is often necessary to generate dates for specific requirements, such as time intervals, random dates, or time series for reports and analysis. In this article, we will explore how to generate dates in SQL using PostgreSQL, providing practical examples and detailed explanations for each technique. For more details, you can refer to the official PostgreSQL documentation.

Generating a Single Date in SQL

If we want to generate a single date in SQL, we can use DATE, TIMESTAMP, and CURRENT_DATE. The basic syntax allows defining an explicit date or retrieving the current system date.

SELECT DATE '2025-02-17';

Returns:

2025-02-17

We can also get the current date using the CURRENT_DATE function, which is useful for generating dynamic reports:

SELECT CURRENT_DATE;

Returns, for example:

2025-02-17

Generating a Date Range with generate_series

If we need to obtain a series of dates between two limits, we can use generate_series. This function generates a set of rows with incremental values between two specified dates. The third parameter defines the interval between each date.

Here is an example to generate dates in SQL for a list of daily values over a month:

SELECT generate_series(
    '2025-02-01'::DATE, 
    '2025-02-28'::DATE, 
    '1 day'::INTERVAL
);

Returns a list of dates from February 1 to February 28, 2025.

If we only want to get weekly dates (every 7 days), we can modify the interval:

SELECT generate_series(
    '2025-02-01'::DATE, 
    '2025-03-31'::DATE, 
    '1 week'::INTERVAL
);

Returns:

2025-02-01
2025-02-08
2025-02-15
...
2025-03-29

Generating Dates with Timestamp

If we want to include time, we can use TIMESTAMP. The interval parameter allows generating timestamps at regular intervals, such as every hour.

SELECT generate_series(
    '2025-02-01 00:00:00'::TIMESTAMP, 
    '2025-02-01 23:59:59'::TIMESTAMP, 
    '1 hour'::INTERVAL
);

Returns a list of hourly timestamps.

Generating Random Dates

To generate random dates, we can use the random() function, which generates a value between 0 and 1, multiplied by a range of days. The result is truncated with trunc() to obtain an integer number added to the starting date.

SELECT '2025-01-01'::DATE + trunc(random() * 60)::int AS random_date;

Returns, for example:

2025-02-10

To generate multiple random dates in a single query:

SELECT '2025-01-01'::DATE + trunc(random() * 60)::int AS random_date
FROM generate_series(1, 10);

Returns 10 random dates within the specified range.

Generating the First or Last Day of the Month

To get the first day of the current month:

SELECT date_trunc('month', CURRENT_DATE)::DATE;

Returns:

2025-02-01

To get the last day of the month:

SELECT (date_trunc('month', CURRENT_DATE) + INTERVAL '1 month - 1 day')::DATE;

Here, date_trunc('month', CURRENT_DATE) truncates the date to the first day of the month, and adding INTERVAL '1 month - 1 day' moves to the last day of the current month.

Returns:

2025-02-29

Practical Use Cases

1. Generating Dates for a Monthly Report

If we need to generate dates in SQL for a report covering all days of a month:

SELECT generate_series(
    date_trunc('month', CURRENT_DATE)::DATE,
    (date_trunc('month', CURRENT_DATE) + INTERVAL '1 month - 1 day')::DATE,
    '1 day'::INTERVAL
);

Returns all dates of the current month.

2. Selecting Only Weekend Data

If we want to filter only Saturdays and Sundays:

SELECT generate_series(
    '2025-02-01'::DATE,
    '2025-02-28'::DATE,
    '1 day'::INTERVAL
) AS date_series
WHERE EXTRACT(DOW FROM date_series) IN (0, 6);

This query uses EXTRACT(DOW FROM date_series), which returns the day of the week (0 for Sunday, 6 for Saturday), filtering the results to include only weekends.

3. Generating Dates for a Promotional Campaign

If a promotion lasts a week with daily offers:

SELECT generate_series(
    '2025-02-10'::DATE, 
    '2025-02-16'::DATE, 
    '1 day'::INTERVAL
);

Returns all dates from February 10 to February 16, 2025.

Conclusion

Generating dates in SQL with PostgreSQL is simple thanks to generate_series, CURRENT_DATE, date_trunc, and other functions. These methods are useful for reports, time-based analysis, and managing date intervals. With the practical examples shown, you can apply these techniques in your projects to automate date-related operations and improve query efficiency. For further reading, you can visit the PostgreSQL official documentation.