SQL provides a DATEADD() function that adds a date or time interval to a specific date and returns the updated date. Similarly, MySQL and MariaDB have DATE_ADD() and ADDDATE() functions to add an interval to a date/time, and SQLite provides a DATE() function that can be used to add an interval to a date/time.
However, PostgreSQL doesn’t provide any equivalent function. Now you must be wondering how to achieve such functionality in PostgreSQL.
Well, nothing to worry about. This post is going to assist you in this regard with the help of practical examples. Let’s start.
How to Add Interval to Datetime in PostgreSQL?
In Postgres, the + or - operators can be used to add or subtract an interval to a Date/time.
Example #1: How to Add Days Into a Date/Time in PostgreSQL?
Suppose we have to add 3 days into the date “2022-08-17”. For this purpose, we utilize + operator as follows:
SELECT DATE '2022-08-17' + INT '3';
Three days have been added to the given date successfully.
Example #2: How to Subtract Days From a Date/Time in PostgreSQL?
Let’s subtract three days from “2022-08-17” using “-” operator:
SELECT DATE '2022-08-17' - INT '3';
Three days have been subtracted from the specified date.
Example #3: How to Add Weeks Into a Date/Time in PostgreSQL?
We will use the INTERVAL data type to add three weeks to the given date:
SELECT DATE '2022-08-17' + INTERVAL '3 WEEKS';
From the output, you can observe that three weeks have been added to the given date.
Example #4: How to Add Months Into a Date/Time in PostgreSQL?
Let’s run the below statement to add two months to the given date:
SELECT DATE '2022-08-17' + INTERVAL '2 MONTHS';
The output proved that using INTERVAL data type, you can add/subtract months to the given date.
Example #5: How to Add Time Into a Date/Time in PostgreSQL?
We can also add time to the given date/time by employing the interval data type. In this example, we will add five hours, 12 minutes to the specified date/time:
SELECT DATE '2022-08-17' + INTERVAL '2 HOURS 12 MINUTES';
This is how you can add/subtract a date or time in a specific date, time.
Example #6: How to Add Date/Time Into a Current Date/Time in PostgreSQL?
Let’s run the below statement to add an interval into the current date:
SELECT CURRENT_DATE + INTERVAL '10 Days 1 Hour 15 Minutes';
In the above query firstly, we utilized the CURRENT_DATE function to get the current date. Next, we utilized the “+” operator with the INTERVAL data type to add an interval to the current date:
The output shows that the specified interval has been added to the current date successfully.
Example #7: How to Add Date/Time to a Table’s Column in PostgreSQL?
We have a table named “sale” in our database. Let’s fetch the table’s records using SELECT statement:
SELECT * FROM sale;
Suppose we want to find out how long the winter and summer sales will last. To do that, we will subtract the sale_start date from the sale_end date:
SELECT sale_end - sale_start FROM sale;
As shown in the output, the winter sale will last 36 days, and the summer sale will last 30 days.
That was all the necessary information regarding how to add an interval to date in PostgreSQL.
Conclusion
In Postgres, the + or - operators are used to add or subtract an interval from a Date/time. In other databases like SQL, MySQL, and MariaDB, different built-in functions are used to add an interval to a date/time. However, in PostgreSQL, you can add an interval to date/time simply by using the “+” and “-” operators. This post considered multiple examples to explain how to add an interval to a date/time.
