In SQL Server, a built-in function named DATEADD() is used to add days to a date. However, Postgres doesn’t support the DATEADD() function. In Postgres, the functionality of the DATEADD() function can be achieved via the “+” operator. The plus "+" operator in Postgres allows us to add specific days to a date field.
How to add days to a date field will be explained in this Postgres blog using Practical examples. So, let’s get started!
Postgres Add Days to Date
To add a specific number of days to a date, users must use the plus “+” operator as follows:
date_field + num_of_days;
The plus operator will retrieve a new date representing the original date plus the specified number of days.
Example 1: Find the Current Date
Let’s first find today’s date using the below-provided command:
SELECT CURRENT_DATE;
The output snippet shows that the current date is “2022-12-22”.
Example 2: Add Five Days to the Current Date
Use the “+” operator to add 5 days to the current date:
SELECT CURRENT_DATE + 5;
The output snippet shows that five days have been added to the current date successfully.
Example 3: Add INTERVAL to the Current Date
You can also add the days to the date using the INTERVAL data type:
SELECT CURRENT_DATE + INTERVAL '3 days';
Three days have been added to the current date successfully.
Example 4: Add 3 Days to a Specific Date Field
You can add days to any specific date field as follows:
SELECT DATE '2022-12-12' + 3;
The output snippet shows that three days have been added to the input date field.
Example 5: Add a Negative Number(Days) to a Specific Date Field
Specifying a negative value will subtract the specified number of days from the given date field:
SELECT DATE '2022-12-12' + INTERVAL '-3 days';
Three days have been subtracted from the given date field. Therefore, to add a negative number to the date field, users must use the “-” operator instead of the “+” operator:
SELECT DATE '2022-12-12' - INTERVAL '-3 days';
The output snippet shows that the negative days have been added to the specified date field successfully.
Example 6: Use + Operator on Table’s Data
Let’s consider a practical scenario to understand the working of the “+” operator in a better way:
CREATE TABLE cnic ( issued_date DATE, valid_until INTERVAL);
A sample table named “cnic” is created with two columns: issued_date and valid_until. Let’s run the below-provided query to insert a couple of records in the “cnic” table:
INSERT INTO cnic(issued_date, valid_until)
VALUES ('2015-01-18', INTERVAL '1825 days'),
('2018-11-01', INTERVAL '1825 days');
Let’s query the data of “cnic” table using the below-provided command:
SELECT * FROM cnic;
Now we will use the “+” operator to find the expiry date of the cnic:
SELECT issued_date + valid_until as expiry_date FROM cnic;
This way, you can add any particular day(s) to a date field in Postgres.
Conclusion
Postgres allows us to add a certain number of days to a date field using the plus “+” operator. In other databases like SQL Server, MySQL, etc., a built-in function named DATEADD() is used to add days to a date. However, Postgres doesn’t support the DATEADD() function. In Postgres, the functionality of the DATEADD() function can be achieved via the “+” operator. This Post explained how to add a certain number of days to a date in Postgres via several examples.
