Often in PostgreSQL you may want to generate a series of months.
You can use the generate_series() function with the following syntax to do so:
SELECT DATE '2024-01-15' + (interval '1' month * generate_series(0,11))
This particular example will generate a series of 12 total dates that are one month apart, using 2024-01-15 as the starting date.
You can change the interval between the dates by changing the interval ‘1’ to a different number.
You can also change the number of total dates generated by changing the range used in the generate_series() function.
The following example shows how to use this syntax in practice.
Example: How to Generate a Series of Months in PostgreSQL
Suppose that we would like to generate a series of 12 dates that are one month apart, using 2024-01-15 as the starting date.
We can use the following syntax to do so:
SELECT DATE '2024-01-15' + (interval '1' month * generate_series(0,11))
?column?
---------------------
2024-01-15 00:00:00
2024-02-15 00:00:00
2024-03-15 00:00:00
2024-04-15 00:00:00
2024-05-15 00:00:00
2024-06-15 00:00:00
2024-07-15 00:00:00
2024-08-15 00:00:00
2024-09-15 00:00:00
2024-10-15 00:00:00
2024-11-15 00:00:00
2024-12-15 00:00:00
Notice that this returns a series of 12 total dates that are each one month apart.
Note that we could change the total number of dates generated by changing the range in the generate_series() function.
For example, we could use the following syntax to instead generate 6 total dates that are each one month apart:
SELECT DATE '2024-01-15' + (interval '1' month * generate_series(0,5))
?column?
---------------------
2024-01-15 00:00:00
2024-02-15 00:00:00
2024-03-15 00:00:00
2024-04-15 00:00:00
2024-05-15 00:00:00
2024-06-15 00:00:00
Notice that this returns a series of 6 total dates that are each one month apart.
Feel free to use similar syntax with the generate_series() function to generate a series of dates that are each one month apart.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Select Range of Rows from Table
PostgreSQL: How to Select the Last Row per Group
PostgreSQL: How to Calculate Difference Between Rows
