Often in PostgreSQL you may want to add a specific number of years to a date column.
You can use the following syntax to do so:
SELECT *,
((start_date) + INTERVAL '1 YEAR') AS year_after
FROM emp;
This particular example adds one year to the date in the column named start_date and saves the result to a new column named year_after.
Note: To add a different number of years, simply change 1 YEAR to a different number, e.g. 3 YEAR.
The following example shows how to use this syntax in practice.
Example: How to Add Years 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 year to each date in the start_date column.
We can use the following syntax to do so:
SELECT *,
((start_date) + INTERVAL '1 YEAR') AS year_after
FROM emp;
empid | start_date | sales | year_after
-------+------------+-------+---------------------
1 | 2021-04-01 | 134 | 2022-04-01 00:00:00
2 | 2022-07-01 | 201 | 2023-07-01 00:00:00
3 | 2022-08-15 | 155 | 2023-08-15 00:00:00
4 | 2022-09-01 | 98 | 2023-09-01 00:00:00
5 | 2023-11-23 | 65 | 2024-11-23 00:00:00
6 | 2024-02-20 | 52 | 2025-02-20 00:00:00
7 | 2024-05-15 | 50 | 2025-05-15 00:00:00
Notice that the resulting table contains a new column named year_after that shows the date from the start_date column with one year 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 YEAR')::date AS year_after
FROM emp;
empid | start_date | sales | year_after
-------+------------+-------+------------
1 | 2021-04-01 | 134 | 2022-04-01
2 | 2022-07-01 | 201 | 2023-07-01
3 | 2022-08-15 | 155 | 2023-08-15
4 | 2022-09-01 | 98 | 2023-09-01
5 | 2023-11-23 | 65 | 2024-11-23
6 | 2024-02-20 | 52 | 2025-02-20
7 | 2024-05-15 | 50 | 2025-05-15
The new column named year_after now only contains the date for the year after the date in the start_date column.
You can use similar syntax to add a specific number of years 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
