Often in PostgreSQL you may want to add a specific number of months to a date column.
You can use the following syntax to do so:
SELECT *,
((start_date) + INTERVAL '1 MONTH') AS month_after
FROM emp;
This particular example adds one month to the date in the column named start_date and saves the result to a new column named month_after.
Note: To add a different number of months, simply change 1 MONTH to a different number, e.g. 3 MONTH.
The following example shows how to use this syntax in practice.
Example: How to Add Months to a Date in PostgreSQL
Suppose that we create the following table named emp that contains information about when various employees started working at some company:
-- create table named 'emp'
CREATE TABLE emp (
empID INT PRIMARY KEY,
start_date DATE,
sales INT
);
-- add rows to the table
INSERT INTO emp VALUES (0001, '2021-04-01', 134);
INSERT INTO emp VALUES (0002, '2022-07-01', 201);
INSERT INTO emp VALUES (0003, '2022-08-15', 155);
INSERT INTO emp VALUES (0004, '2022-09-01', 98);
INSERT INTO emp VALUES (0005, '2023-11-23', 65);
INSERT INTO emp VALUES (0006, '2024-02-20', 52);
INSERT INTO emp VALUES (0007, '2024-05-15', 50);
-- view all rows in table
SELECT * FROM emp;
empid | start_date | sales
-------+------------+-------
1 | 2021-04-01 | 134
2 | 2022-07-01 | 201
3 | 2022-08-15 | 155
4 | 2022-09-01 | 98
5 | 2023-11-23 | 65
6 | 2024-02-20 | 52
7 | 2024-05-15 | 50
Suppose that we would like to create a new column by adding 1 month to each date in the start_date column.
We can use the following syntax to do so:
SELECT *,
((start_date) + INTERVAL '1 MONTH') AS month_after
FROM emp;
empid | start_date | sales | month_after
-------+------------+-------+---------------------
1 | 2021-04-01 | 134 | 2021-05-01 00:00:00
2 | 2022-07-01 | 201 | 2022-08-01 00:00:00
3 | 2022-08-15 | 155 | 2022-09-15 00:00:00
4 | 2022-09-01 | 98 | 2022-10-01 00:00:00
5 | 2023-11-23 | 65 | 2023-12-23 00:00:00
6 | 2024-02-20 | 52 | 2024-03-20 00:00:00
7 | 2024-05-15 | 50 | 2024-06-15 00:00:00
The resulting table contains a new column named month_after that shows the date from the start_date column with one month added to it.
If you’d like, you can also cast the result to a date to only display the date without the time:
SELECT *,
((start_date) + INTERVAL '1 MONTH')::date AS month_after
FROM emp;
empid | start_date | sales | month_after
-------+------------+-------+-------------
1 | 2021-04-01 | 134 | 2021-05-01
2 | 2022-07-01 | 201 | 2022-08-01
3 | 2022-08-15 | 155 | 2022-09-15
4 | 2022-09-01 | 98 | 2022-10-01
5 | 2023-11-23 | 65 | 2023-12-23
6 | 2024-02-20 | 52 | 2024-03-20
7 | 2024-05-15 | 50 | 2024-06-15
The new column named month_after now only contains the date for the month after the date in the start_date column.
You can use similar syntax to add a specific number of months to a date column in your own table.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Select All Records from Yesterday
PostgreSQL: Select All Records from Last 7 Days
PostgreSQL: Calculate Difference Between Dates in Days
