Dates and times

What replaces DATEADD in PostgreSQL?

By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17

PostgreSQL has no DATEADD. To add days, add a number: DATE '2026-01-15' + 90 returns 2026-04-15, and the result is still a date.

To add months, years or hours, add an interval: DATE '2026-01-15' + INTERVAL '1 month'. You get a timestamp back, so cast it with ::date when you want a date. To subtract, use a minus sign.

The worked examples below run against one table, employees, which holds one row per employee with a hire_date. Its columns are below.

You can edit and re-run two of the queries in the editors here, and there are two exercises at the end. See our other guides to SQL dates in PostgreSQL.

Schema · hr1 table? = nullable

employees

idinteger

nametext

emailtext

department_id?integer

manager_id?integer

hire_datedate

titletext

is_activeboolean

employees	first_hire	last_hire
60	2018-01-15	2022-09-01

the shape of the table every date below is drawn from

What happens if you run DATEADD in PostgreSQL?

Run it and PostgreSQL complains about a missing column named day. Here is the SQL Server habit:

SELECT DATEADD(day, 30, hire_date)
FROM employees;

PostgreSQL responds column "day" does not exist

The missing column is not a side effect of the missing function. PostgreSQL works out what each argument is before it looks for a function to pass them to, and in an argument position a bare word is an identifier. So it looks for a column called day, finds none in employees, and stops there. It never gets as far as telling you that DATEADD does not exist.

You can check the order for yourself, in both directions. Hand the same statement a day column and PostgreSQL reaches the next step, the missing function:

SELECT DATEADD(day, 30, hire_date)
FROM (VALUES (1, DATE '2024-01-01')) AS t(day, hire_date);

PostgreSQL responds function dateadd(integer, integer, date) does not exist

Now the other direction. Define your own dateadd() function and you still hit the same missing column, because PostgreSQL never reaches your function. Writing one does not buy you the spelling Microsoft documents as DATEADD ( datepart , number , date ).

Try the spelling MySQL documents as DATE_ADD(date, INTERVAL expr unit) and you fail even sooner, in the parser rather than at the column lookup. PostgreSQL has an INTERVAL n UNIT form of its own and wants the number quoted: INTERVAL '30' DAY is an interval of 30 days, and PostgreSQL rejects INTERVAL 30 DAY in the parser, before it looks up a single name:

SELECT DATE_ADD(hire_date, INTERVAL 30 DAY)
FROM employees;

PostgreSQL responds syntax error at or near "30"

SELECT DATEADD(day, 30, hire_date)
FROM employees;

That error is about the interval syntax and not about the name. PostgreSQL does ship a date_add() function from version 16 on, and DATE_ADD(hire_date, INTERVAL '30' DAY) runs here and returns 2018-02-14 00:00:00+00 for the first employee. It hands back a timestamp with time zone rather than a date, which is the reason the + operator is still the better habit. There is a section on date_add() at the foot of this page.

How do you add days to a date in SQL?

Add a whole number to the date. In PostgreSQL an integer on the right of a date is a number of days, so hire_date + 90 is 90 days after the hire date. Sort by hire date, newest first, and take six to see when the newest hires finish a 90-day probation. Two of them share a hire date, which is why the sort carries name as a second key: a single-column ORDER BY leaves tied rows in no defined order, so which of the two comes back first can change between runs. Adding a second key only settles it while the pair of columns is unique, and in this table it is.

SELECT name,
       hire_date,
       hire_date + 90 AS probation_ends
FROM employees
ORDER BY hire_date DESC, name
LIMIT 6;

name	hire_date	probation_ends
Casey Davis	2022-09-01	2022-11-30
Xander York	2022-07-01	2022-09-29
Blake Carter	2022-06-15	2022-09-13
Abe Bell	2022-05-01	2022-07-30
Mark Nash	2022-04-01	2022-06-30
Wendy Xu	2022-04-01	2022-06-30

date + 90 is still a date

Subtract the same way: hire_date - 7 for a week earlier.

Try it: change hire_date + 90 to hire_date + INTERVAL '90 days' and run it again. Same dates, but now you also get a time of 00:00:00 on each one. Read on for why.

SELECT name,
       hire_date,
       hire_date + 90 AS probation_ends
FROM employees
ORDER BY hire_date DESC, name
LIMIT 6;

Why did adding an interval turn my date into a timestamp?

Because the two forms return different types. Add an integer and you get a date. Add an interval and you get a timestamp, with no time zone on it. PostgreSQL does not warn you, so you end up with a different column type than you started with:

SELECT pg_typeof(DATE '2024-01-15' + 1) AS date_plus_integer,
       pg_typeof(DATE '2024-01-15' + INTERVAL '1 day') AS date_plus_interval;

date_plus_integer	date_plus_interval
date	timestamp without time zone

An interval can hold hours, minutes and seconds as well as days, and there is nowhere in a date to put them, so PostgreSQL widens the result. Both rows are in PostgreSQL’s own date and time operators table: date + integer → date, and date + interval → timestamp. Cast it when you want a date back:

SELECT name,
       hire_date,
       hire_date + INTERVAL '1 year' AS anniversary,
       (hire_date + INTERVAL '1 year')::date AS anniversary_date
FROM employees
ORDER BY id
LIMIT 3;

name	hire_date	anniversary	anniversary_date
Sarah Chen	2018-01-15	2019-01-15 00:00:00	2019-01-15
Marcus Reid	2018-03-01	2019-03-01 00:00:00	2019-03-01
Priya Sharma	2018-06-01	2019-06-01 00:00:00	2019-06-01

the same anniversary, as a timestamp and as a date

You notice this when you compare the result, export it, or join it against a date column. Compare a timestamp at midnight with a date and you get a match. Try 09:30 on the same day and you do not.

Is adding one month the same as adding 30 days?

No: add INTERVAL '1 month' and you land on the same day of the next month, while 30 days is always 30 days. Start from January 31 and you end up in different months:

SELECT DATE '2024-01-31' + 30 AS plus_30_days,
       DATE '2024-01-31' + INTERVAL '1 month' AS plus_1_month;

plus_30_days	plus_1_month
2024-03-01	2024-02-29 00:00:00

2024 is a leap year, so February has 29 days

When the target month is too short for the day you started on, PostgreSQL pins the result to that month's last day. Add a month to January 29, 30 and 31 of 2024 and all three land on February 29, but only two of them were pinned: 2024 is a leap year, so the 29th keeps its own day of the month, while the 30th and the 31st have nowhere to land. March 31 goes to April 30 the same way:

SELECT start_date,
       (start_date + INTERVAL '1 month')::date AS one_month_later
FROM (VALUES (DATE '2024-01-29'),
             (DATE '2024-01-30'),
             (DATE '2024-01-31'),
             (DATE '2024-03-31')) AS t(start_date)
ORDER BY start_date;

start_date	one_month_later
2024-01-29	2024-02-29
2024-01-30	2024-02-29
2024-01-31	2024-02-29
2024-03-31	2024-04-30

four start dates; PostgreSQL clamps January 30 and 31 onto February 29, and March 31 onto April 30

Add years from a leap day and you see the same thing:

SELECT DATE '2024-02-29' + INTERVAL '1 year' AS one_year_later,
       DATE '2024-02-29' + INTERVAL '4 years' AS four_years_later;

one_year_later	four_years_later
2025-02-28 00:00:00	2028-02-29 00:00:00

This is not a bug. It is the answer PostgreSQL picked for a month with no day 31, and you pick which question you are asking. When your rule is 30 days, add 30. When your rule is one month, add the interval and expect month-end dates to move.

How do you add a number of days or months from another column?

For days, add the column directly. PostgreSQL treats an integer column exactly like a literal integer, so you still get a date back:

SELECT plan,
       trial_days,
       DATE '2026-03-14' + trial_days AS trial_ends
FROM (VALUES ('basic', 7), ('pro', 14), ('team', 30)) AS plans(plan, trial_days)
ORDER BY trial_days;

plan	trial_days	trial_ends
basic	7	2026-03-21
pro	14	2026-03-28
team	30	2026-04-13

a date plus an integer column is a date

For months or any other unit, multiply the column by a one-unit interval. Building the interval from text works too, for example (months || ' months')::interval, but months * INTERVAL '1 month' is shorter and keeps the value a number the whole way:

SELECT plan,
       months,
       DATE '2026-03-14' + months * INTERVAL '1 month' AS renews_on
FROM (VALUES ('monthly', 1), ('quarterly', 3), ('annual', 12)) AS plans(plan, months)
ORDER BY months;

plan	months	renews_on
monthly	1	2026-04-14 00:00:00
quarterly	3	2026-06-14 00:00:00
annual	12	2027-03-14 00:00:00

months * INTERVAL '1 month'

With make_interval() you name the units, which is easier to read once you combine more than one of them. Here is the same renewal date, plus a three-day grace period:

SELECT plan,
       months,
       DATE '2026-03-14' + make_interval(months => months) AS renews_on,
       DATE '2026-03-14' + make_interval(months => months, days => 3) AS grace_ends
FROM (VALUES ('monthly', 1), ('quarterly', 3), ('annual', 12)) AS plans(plan, months)
ORDER BY months;

plan	months	renews_on	grace_ends
monthly	1	2026-04-14 00:00:00	2026-04-17 00:00:00
quarterly	3	2026-06-14 00:00:00	2026-06-17 00:00:00
annual	12	2027-03-14 00:00:00	2027-03-17 00:00:00

the same renews_on values as the query above, with a grace date beside them

How do you subtract days or months from a date?

Use a minus sign with either form. The type rule is the same: with an integer you keep a date, with an interval you get a timestamp.

SELECT DATE '2024-03-31' - 1 AS minus_1_day,
       DATE '2024-03-31' - INTERVAL '1 month' AS minus_1_month;

minus_1_day	minus_1_month
2024-03-30	2024-02-29 00:00:00

March 31 minus one month is the last day of February

Does PostgreSQL 16 have a date_add function?

Yes, since version 16, which added date_add() and date_subtract(). Both take a timestamp with time zone and return one, so pass a date and PostgreSQL casts it first, handing back a type you did not start with:

SELECT hire_date,
       date_add(hire_date, INTERVAL '30 days') AS plus_30_days,
       pg_typeof(date_add(hire_date, INTERVAL '30 days')) AS type
FROM employees
ORDER BY id
LIMIT 1;

hire_date	plus_30_days	type
2018-01-15	2018-02-14 00:00:00+00	timestamp with time zone

this sandbox runs in UTC, hence +00

The time zone is part of the arithmetic, not just the display. Add a third argument naming the zone to do the arithmetic in and you get both answers side by side. New York enters daylight saving time on March 10, 2024, so three days from 2024-03-08 00:00 UTC, which is 19:00 on March 7 in New York, is 71 hours there and 72 in UTC:

SELECT date_add(TIMESTAMPTZ '2024-03-08 00:00+00', INTERVAL '3 days', 'UTC') AS in_utc,
       date_add(TIMESTAMPTZ '2024-03-08 00:00+00', INTERVAL '3 days', 'America/New_York') AS in_new_york;

in_utc	in_new_york
2024-03-11 00:00:00+00	2024-03-10 23:00:00+00

two different instants, both printed in UTC because this sandbox runs in UTC

Leave the third argument out and PostgreSQL does the arithmetic in your session’s TimeZone. PostgreSQL can answer differently in two sessions when the interval spans a daylight saving change in one zone and not the other: drop the third argument from the query above and PostgreSQL lands an hour earlier in a New York session than in a UTC or a Tokyo one. Run that same two-argument call over thirty days from 2024-01-01 00:00 UTC, where no such change falls inside the interval, and PostgreSQL returns 2024-01-31 00:00 UTC in all three of those sessions. For everyday date maths, reach for the + operator. Add an integer to a date and you get a date back. Add an interval to a date and you get a timestamp with no time zone, which is the trade the rest of this page is about. Both of those start from a date: add an interval to a timestamp with time zone and that is what comes back, which is also what date_add() returns whatever you give it. For the opposite problem, the gap between two dates, see what to use instead of DATEDIFF.

Practice: add days and months to a date

Adding to dates is part of date arithmetic and intervals of our free SQL course, where you also learn how to build and compare intervals. For what separates a date from a timestamp in the first place, see the date and time types. No account, nothing to install, nothing to pay.