Often in PostgreSQL you may want to subtract a specific number of days from a date column.
You can use the following syntax to do so:
SELECT *,
((start_date) - INTERVAL '1 DAY') AS day_before
FROM emp;
This particular example subtracts one day from the date in the column named start_date and saves the result to a new column named day_before.
Note: To subtract a different number of days, simply change 1 DAY to a different number, e.g. 5 DAY.
The following example shows how to use this syntax in practice.
Example: How to Subtract Days 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 day from each date in the start_date column.
We can use the following syntax to do so:
SELECT *,
((start_date) - INTERVAL '1 DAY') AS day_before
FROM emp;
empid | start_date | sales | day_before
-------+------------+-------+---------------------
1 | 2021-04-01 | 134 | 2021-03-31 00:00:00
2 | 2022-07-01 | 201 | 2022-06-30 00:00:00
3 | 2022-08-15 | 155 | 2022-08-14 00:00:00
4 | 2022-09-01 | 98 | 2022-08-31 00:00:00
5 | 2023-11-23 | 65 | 2023-11-22 00:00:00
6 | 2024-02-20 | 52 | 2024-02-19 00:00:00
7 | 2024-05-15 | 50 | 2024-05-14 00:00:00
The resulting table contains a new column named day_before that shows the date from the start_date column with one day 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 DAY')::date AS day_before
FROM emp;
empid | start_date | sales | day_before
-------+------------+-------+------------
1 | 2021-04-01 | 134 | 2021-03-31
2 | 2022-07-01 | 201 | 2022-06-30
3 | 2022-08-15 | 155 | 2022-08-14
4 | 2022-09-01 | 98 | 2022-08-31
5 | 2023-11-23 | 65 | 2023-11-22
6 | 2024-02-20 | 52 | 2024-02-19
7 | 2024-05-15 | 50 | 2024-05-14
The new column named day_before now only contains the date for the day before the date in the start_date column.
Feel free to use similar syntax to subtract a specific number of days 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
