PostgreSQL DATEADD: Add Days, Months, Hours

AAI for Database TeamSEP 29 2026 · 6 MIN

TL;DR: PostgreSQL has no DATEADD function. Add days with date + integer (date '2001-09-28' + 7 gives 2001-10-05), add any other unit with + interval '3 months', and use make_interval() or n * interval '1 day' when the amount comes from a column. PostgreSQL 16 added date_add() and date_subtract() for time-zone-aware arithmetic on timestamptz.

The PostgreSQL equivalent of SQL Server's DATEADD(day, 7, created_at) is created_at + interval '7 days', and for plain date values simply created_at + 7. PostgreSQL does date arithmetic with the + and - operators and the interval type instead of a DATEADD function, so DATEADD(month, 3, d) becomes d + interval '3 months' and DATEADD(hour, -2, ts) becomes ts - interval '2 hours'. The sections below cover each unit, variable amounts, month-end behavior, time zones, and the PostgreSQL 16 date_add() function that most DATEADD tutorials still don't mention.

Is there a DATEADD function in PostgreSQL?

No. DATEADD is a SQL Server (Transact-SQL) function, and MySQL uses DATE_ADD(date, INTERVAL 1 DAY). PostgreSQL uses operators on date, timestamp, timestamptz and interval values, and since version 16 also offers date_add(timestamptz, interval [, time_zone]). Here is the mapping:

SQL Server | MySQL | PostgreSQL

DATEADD(day, 7, d) | DATE_ADD(d, INTERVAL 7 DAY) | d + 7 (date) or d + interval '7 days'

DATEADD(week, 2, d) | DATE_ADD(d, INTERVAL 2 WEEK) | d + interval '2 weeks'

DATEADD(month, 3, d) | DATE_ADD(d, INTERVAL 3 MONTH) | d + interval '3 months'

DATEADD(year, 1, d) | DATE_ADD(d, INTERVAL 1 YEAR) | d + interval '1 year'

DATEADD(hour, -2, ts) | DATE_SUB(ts, INTERVAL 2 HOUR) | ts - interval '2 hours'

DATEADD(minute, 30, ts) | DATE_ADD(ts, INTERVAL 30 MINUTE) | ts + interval '30 minutes'

GETDATE() | NOW() | now() / current_date

How do you add days to a date in PostgreSQL?

Adding an integer to a date adds that many days and returns a date. The PostgreSQL manual's example is date '2001-09-28' + 7 → 2001-10-05, and date '2001-10-01' - 7 → 2001-09-24 subtracts.

SELECT current_date + 30 AS in_30_days,
       current_date - 90 AS ninety_days_ago;

 Trials that end within the next 7 days
SELECT id, email, trial_started_on + 14 AS trial_ends_on
FROM accounts
WHERE trial_started_on + 14 BETWEEN current_date AND current_date + 7;

Watch the result type. date + integer returns a date, but date + interval returns a timestamp: date '2001-09-28' + interval '1 hour' gives 2001-09-28 01:00:00. Cast with ::date if you need a date back, for example (signup_date + interval '1 month')::date.

How do you add months, years, hours or minutes?

Add an interval literal. Interval strings accept microseconds, milliseconds, seconds, minutes, hours, days, weeks, months, years, decades, centuries and millennia, and you can combine units in one literal:

SELECT timestamp '2001-09-28 01:00' + interval '23 hours';    , 2001-09-29 00:00:00
SELECT now() + interval '1 year 2 months 3 days';
SELECT now() - interval '15 minutes';                         , last 15 minutes
SELECT created_at + interval '1.5 days' FROM orders;          , adds 1 day 12:00:00

That last line is a difference from SQL Server: Microsoft documents that DATEADD truncates a fractional number (it does not round), so DATEADD(day, 1.5, d) adds one day. PostgreSQL keeps the fraction.

How do you add a variable number of days from a column?

Interval literals like interval '7 days' must be constants, so writing interval 'n days' with a column name does not work. Use one of these instead:

 Multiply a one-unit interval (works in every supported version)
SELECT start_date + trial_days * interval '1 day' AS trial_end FROM plans;

 make_interval with named arguments
SELECT start_date + make_interval(months => term_months) AS renewal_date
FROM contracts;

 For date columns and whole days, plain integer addition is simplest
SELECT start_date + trial_days AS trial_end FROM plans;

make_interval takes years, months, weeks, days, hours, mins and secs; the manual's example make_interval(days => 10) returns 10 days. It is the cleanest choice when several parts come from different columns.

What happens when you add a month to January 31?

PostgreSQL clamps to the last day of the target month: date '2024-01-31' + interval '1 month' returns 2024-02-29 00:00:00 (2024 is a leap year) and in 2025 returns 2025-02-28. SQL Server's DATEADD behaves the same way. The catch is chaining: adding one month twice is not the same as adding two months.

SELECT (date '2024-01-31' + interval '1 month') + interval '1 month'; , 2024-03-29
SELECT  date '2024-01-31' + interval '2 months';                      , 2024-03-31

For billing schedules, always compute each renewal from the original anchor date (anchor + n * interval '1 month') rather than from the previous renewal, or the day of month drifts after the first short month.

How does adding time work with time zones and daylight saving?

For timestamptz, adding interval '1 day' moves the local calendar day in the session's TimeZone setting, while interval '24 hours' adds exactly 24 hours of elapsed time. Across a daylight-saving change these differ by one hour. PostgreSQL 16 added date_add() and date_subtract() so you can name the time zone explicitly instead of depending on the session setting. The manual's example:

SELECT date_add('2021-10-31 00:00:00+02'::timestamptz, '1 day'::interval, 'Europe/Warsaw');
 2021-10-31 23:00:00+00

Warsaw left summer time on 31 October 2021, so "one day later" in that zone is 25 hours of real time, and date_add accounts for it. date_subtract('2021-11-01 00:00:00+01'::timestamptz, '1 day'::interval, 'Europe/Warsaw') returns 2021-10-30 22:00:00+00. On PostgreSQL 15 and older, get the same result with SET TimeZone = 'Europe/Warsaw' before the arithmetic, or convert with AT TIME ZONE.

What are common DATEADD patterns in reporting queries?

These are the date-add expressions that show up most in dashboards and alerts:

Question | PostgreSQL expression

Rows from the last 30 days | WHERE created_at >= now() - interval '30 days'

Since the start of this month | WHERE created_at >= date_trunc('month', now())

Last full calendar month | WHERE created_at >= date_trunc('month', now()) - interval '1 month' AND created_at < date_trunc('month', now())

Last day of the current month | (date_trunc('month', now()) + interval '1 month' - interval '1 day')::date

Renewal date from an anchor | anchor_date + n * interval '1 month'

Every day in a range | generate_series(date '2024-09-01', date '2024-09-30', interval '1 day')

Filter with created_at >= now() - interval '30 days' rather than created_at + interval '30 days' >= now(): keeping the column bare lets PostgreSQL use an index on created_at. To measure the gap between two dates instead of shifting one, see our guide to DATEDIFF in PostgreSQL, and for churn windows built on these filters see SaaS churn queries for Postgres.

If your team asks these questions more often than it writes SQL, AI for Database turns "signups in the last full calendar month by plan" into the query, runs it against PostgreSQL, and shows the generated SQL so the date window can be checked. Setup is in the PostgreSQL docs.

Sources: PostgreSQL date/time functions and operators, PostgreSQL 16 release notes, Microsoft DATEADD (Transact-SQL).

FAQ

How do I subtract days from the current date in PostgreSQL?

current_date - 7 returns the date a week ago; now() - interval '7 days' returns a timestamp with the time of day kept.

Why does adding an interval to a date return a timestamp?

Because an interval can contain hours and minutes, date + interval is defined to return timestamp. Cast the result with ::date.

How do I add a number of months stored in a column?

Use make_interval(months => col) or col * interval '1 month'. String literals such as interval 'col months' cannot reference columns.

Does PostgreSQL have DATE_ADD like MySQL?

Only for timestamptz, since version 16, and with a different signature: date_add(ts, interval '1 day', 'Europe/Warsaw'). MySQL's DATE_ADD(d, INTERVAL 1 DAY) syntax does not run in PostgreSQL.