Often in PostgreSQL you may want to generate a date series between two specific dates.
You can use the generate_series function with the following syntax to do so:
SELECT generate_series(timestamp '2024-01-01', '2024-01-15', '1 day')::date AS day;
This particular example will generate a date series ranging from 2024-01-01 to 2024-01-15, using days as the interval.
Note: You can use a different interval by changing 1 day to a different unit of time such as 1 month.
The following example shows how to use this syntax in practice.
Example: How to Generate Date Series Between Two Dates in PostgreSQL
Suppose that we would like to generate a series of days between 2024-01-01 and 2024-01-15.
We can use the following syntax to do so:
SELECT generate_series(timestamp '2024-01-01', '2024-01-15', '1 day')::date AS day;
day
------------
2024-01-01
2024-01-02
2024-01-03
2024-01-04
2024-01-05
2024-01-06
2024-01-07
2024-01-08
2024-01-09
2024-01-10
2024-01-11
2024-01-12
2024-01-13
2024-01-14
2024-01-15
The result is a series of days ranging from 2024-01-01 to 2024-01-15, just as we specified.
Note that we could also specify a different interval to use.
For example, we could use the following syntax to generate a date series using 1 month as the interval instead:
SELECT generate_series(timestamp '2024-01-01', '2024-12-01', '1 month')::date AS month;
month
------------
2024-01-01
2024-02-01
2024-03-01
2024-04-01
2024-05-01
2024-06-01
2024-07-01
2024-08-01
2024-09-01
2024-10-01
2024-11-01
2024-12-01
The result is a date series that ranges from 2024-01-01 to 2024-12-01, using one month as the interval between each consecutive date.
Feel free to use similar syntax to generate any date series that you would like.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Extract Date from a Timestamp
PostgreSQL: How to Subtract Days from a Date
PostgreSQL: How to Select Rows in Date Range
