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