---
title: "PostgreSQL DATEADD: Add Days, Months, Hours"
description: "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…"
canonical: "https://www.aifordatabase.com/blog/postgresql-dateadd/"
author: "Dhrumil Barot"
author_bio: "Dhrumil is an enterprise analytics expert. Dhrumil built AI for Database so anyone on a team can get answers from live company data in plain English, without waiting on a data team."
published: 2026-09-29
updated: 2026-09-29
---

# PostgreSQL DATEADD: Add Days, Months, Hours

**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](https://www.aifordatabase.com/blog/postgresql-datediff/), and for churn windows built on these filters see [SaaS churn queries for Postgres](https://www.aifordatabase.com/answers/saas-churn-postgres/).

If your team asks these questions more often than it writes SQL, [AI for Database](https://www.aifordatabase.com/databases/postgresql/) 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](https://www.aifordatabase.com/docs/postgresql/).

Sources: [PostgreSQL date/time functions and operators](https://www.postgresql.org/docs/current/functions-datetime.html), [PostgreSQL 16 release notes](https://www.postgresql.org/docs/16/release-16.html), [Microsoft DATEADD (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/functions/dateadd-transact-sql).

D

Written by [Dhrumil Barot](https://www.aifordatabase.com/about/#founder)

Dhrumil is an enterprise analytics expert. Dhrumil built AI for Database so anyone on a team can get answers from live company data in plain English, without waiting on a data team.

[LinkedIn](https://www.linkedin.com/in/bdhrumil)[X](https://x.com/fyidhrumil)

## Frequently Asked Questions

### 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.

Read next

*   [POSTGRES TIMESTAMPTZ: WHAT IT STORES AND WHEN TO USE IT →](https://www.aifordatabase.com/blog/postgres-timestamptz/)
*   [HOW TO FIND DUPLICATES IN SQL (AND DELETE THEM SAFELY) →](https://www.aifordatabase.com/blog/find-duplicates-sql-query/)
*   [POSTGRESQL GETDATE() EQUIVALENT: NOW() AND SYSDATE →](https://www.aifordatabase.com/blog/postgresql-getdate/)

Source: https://www.aifordatabase.com/blog/postgresql-dateadd/