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