In databases like SQL, MySQL, MariaDB, etc., users can add intervals to DateTime values using built-in functions. For instance, DATEADD() in SQL Server, DATE_ADD() in MySQL, ADDDATE() in MariaDB, etc. However, In Postgres, there is no such function that offers the same functionality. Now the question is how to Add intervals to the DateTime value in Postgres. Well! Nothing to worry about because Postgres allows us to add intervals to date time using the “+” and “-” operators.

This post will demonstrate:

• How Do I Add an Interval to the Current Date in Postgres?
• How Do I Subtract/Deduct an Interval From the Current Date in Postgres?
• How to Add Intervals to Particular DateTime Values?
• How to Subtract Intervals From Particular DateTime Values?
• How to Add or Subtract an Interval From the Table’s Data?

How Do I Add an Interval to the Current Date in Postgres?

In the following code snippet, the “+” operator is used to add an interval to the current DateTime:

SELECT NOW(), NOW() + INTERVAL '1 Month 2 Days 3 Hours';

In the above snippet, the NOW() function is first used to get the current DateTime. After that, an interval “1 Month, 2 Days and 3 Hours” is added to the current DateTime using the “+” operator:

The output shows the current DateTime and current DateTime after 1 month, 2 days, and 3 hours.

How Do I Subtract/Deduct an Interval From the Current Date in Postgres?

In the below code, the “-” operator is used to subtract an interval from the current DateTime:

SELECT NOW(), NOW() - INTERVAL '1 Month 2 Days 3 Hours';

In the above snippet, an interval “1 Month, 2 Days and 3 Hours” is subtracted from the current DateTime using the “-” operator:

The output shows the current DateTime and current DateTime before 1 month, 2 days, and 3 hours.

How to Add Intervals to Particular DateTime Values?

Let’s use the “+” operator to add an interval to a specific DateTime:

SELECT DATE '2015-07-12' + INTERVAL '3 Month 5 Days 3 Hours 2 Minutes';

In the above snippet, an interval “1 Month, 2 Days and 3 Hours” is added to a date “2015-07-12” using the “+” operator:

The specified interval has been added to the given date using the “+” operator.

How to Subtract Intervals From Particular DateTime Values?

The below example demonstrates the usage of the “-” operator to subtract an interval from a specific DateTime:

SELECT DATE '2015-07-12' - INTERVAL '3 Month 5 Days 3 Hours 2 Minutes';

In the above snippet, an interval “1 Month, 2 Days and 3 Hours” is added to a date “2015-07-12” using the “+” operator:

The specified interval has been subtracted from the given date using the “-” operator.

How to Add or Subtract an Interval From a Table’s Data?

The below snippet shows the staff information, including the joining date and contract duration:

Suppose we have to find the contract expiry date for each staff member. To do that, we will execute the following code:

SELECT staff_name, joining_date + contract_duration AS contract_expiry_date
FROM staff_info;

This way, you can attain the functionality of the DATEADD function in Postgres.

Conclusion

Postgres allows us to add or subtract intervals to date time using the “+” and “-” operators. Other than these operators, there is no alternative way in Postgres to add or remove intervals from a DateTime field. This post demonstrated various examples of adding intervals to DateTime values.