Alternatives to DATEADD() in PostgreSQL

PostgreSQL does not use SQL Server’s DATEADD() syntax. For a whole number of days, add that integer to a date, as in manufacture_date + expires_in. For calendar units such as months or years, use an INTERVAL, for example date '2022-08-24' + INTERVAL '2 months'. The first expression returns a date; adding an interval to a date returns a timestamp without time zone.

The DATEADD() Function in SQL

A product table may store a manufacture date and an integer number of days until expiration. For example, a row could contain manufacture_date = DATE '2022-08-24' and expires_in = 10. The goal is to calculate the expiration date from those two values.

DATEADD() is the SQL Server function commonly used for this task. Its SQL Server form is DATEADD(day, 10, '2022-08-24'): the first argument names a date part, the second gives the amount, and the third is the starting date. This syntax is a comparison for readers arriving from SQL Server; it is not a PostgreSQL query. PostgreSQL expresses date arithmetic with operators and intervals instead.

The distinction matters because “add days” and “add a calendar month” are different operations. A day count is a fixed number of calendar days. A month has a variable number of days, so converting months to a fixed day count can produce the wrong calendar date. Choose the PostgreSQL form that matches the unit you need.

Use the + and - Operators as an Alternative to DATEADD() in PostgreSQL

For day offsets, PostgreSQL lets you add an integer directly to a date. The expression DATE '2022-08-24' + 10 calculates a date ten days later. Subtract days with DATE '2022-09-03' - 10, or use a negative integer with addition, as in DATE '2022-09-03' + (-10). These are date expressions; the result remains a date and does not add a time of day.

This concise approach is useful when the offset is a number of days, including an integer column such as expires_in. For example, manufacture_date + expires_in calculates an expiration date for each product row. Ensure the stored value represents days: an integer added to a date does not mean months or years. If the amount is a parameter, use a parameter type compatible with integer arithmetic or cast it to an integer when appropriate.

The following Python example uses the same manufacture date and stored day count to calculate the expiration date:

from datetime import date, timedelta

manufacture_date = date(2022, 8, 24)
expires_in = 10
expiration_date = manufacture_date + timedelta(days=expires_in)
print(expiration_date.isoformat())

2022-09-03

This Python example illustrates the date calculation; it does not execute PostgreSQL SQL. In a PostgreSQL query, use the date expression shown above.

Use an interval when the calculation is expressed in calendar months, years, weeks, or a mixture of units. This keeps the intended unit visible in the expression and avoids pretending that every month has a fixed number of days.

Use the INTERVAL Data Type as an Alternative to DATEADD() in PostgreSQL

An INTERVAL represents a span of time. Add one to a date to express calendar-based arithmetic: DATE '2022-08-24' + INTERVAL '10 days', DATE '2022-08-24' + INTERVAL '2 months', or DATE '2022-08-24' + INTERVAL '1 year'. Week units are also accepted, as in DATE '2022-08-24' + INTERVAL '1 week'. PostgreSQL permits singular and plural unit words in interval literals.

An interval can combine units, such as INTERVAL '1 week 2 days'. This is useful when an offset comes from several calendar components. The interval can also include hours, minutes, and seconds. When an interval is added to a date, PostgreSQL returns a timestamp without time zone, so the displayed value includes a time component, normally midnight. If the application needs a date afterward, cast the result to date deliberately.

Calendar units follow calendar arithmetic rather than fixed-day conversion. Adding one month to a date near the end of a long month may land on the last valid day of the next month. The result can therefore differ from adding a fixed number of days. Test the expression with dates representative of the data and choose months or days according to the business rule.

For the product example, multiply the stored day count by a one-day interval: manufacture_date + expires_in * INTERVAL '1 day'. This works row by row and returns a timestamp because the interval participates in the expression. If a date-valued result is required, use (manufacture_date + expires_in * INTERVAL '1 day')::date. For a stored number of months, use the same pattern with INTERVAL '1 month'; use an interval unit that matches what the column means.

PostgreSQL examples in this article are supplied as SQL expressions, but the configured article runtime does not execute PostgreSQL statements. Treat them as unexecuted SQL illustrations and run them against your PostgreSQL database. DATEADD() appears only to explain the SQL Server equivalent; use PostgreSQL’s +, -, and INTERVAL forms in PostgreSQL queries.