Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

Adding months to a date in PostgreSQL fails or returns the wrong type

ErrorAdding months to a date in PostgreSQL gives a syntax error

Short answer

PostgreSQL has no add_months function. Add an interval instead: date + interval '1 month'. The unit must sit inside the quoted literal. For a variable count, multiply interval '1 month' by the number or use make_interval. Date plus interval returns a timestamp, so cast to date if you need a date.

  • Applies to PostgreSQL 16
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Run the shortest correct form and compare it with your expression.

    SELECT date '2001-09-28' + interval '1 month';
  2. 2Match what you see, then jump to its fix

    1. 1Expressions like date + 1 month or date + 1 monthsThe month unit is outside the quotes, or a bare number is addedFix
    2. 2interval '$1 month' or interval 'n month' with a variable inside the quotesThe month count comes from a column or parameterFix
    3. 32001-04-30 00:00:00 where you expected a date, or a day that changedThe result carries a time part, or the end of month movedFix

What does the error look like?

  • Adding months to a date in PostgreSQL gives a syntax error
  • The result of date + interval shows a time part

Cause 1 of 3

The month unit is outside the quotes, or a bare number is added

1Check

An interval literal is a string such as '1 month' or '1 year 2 months'. Date and time literals must be enclosed in single quotes. A date plus a bare integer adds days, not months.

SELECT date '2001-09-28' + 7;

2Fix

Write the quantity and unit together inside the quotes.

SELECT date '2001-09-28' + interval '1 month';
SELECT date '2001-09-28' + interval '1 year 2 months';

3Verify

The query returns the shifted date.

SELECT date '2001-09-28' + interval '2 months';

Confirmed by postgresql.org

Did this fix it?

Cause 2 of 3

The month count comes from a column or parameter

1Check

Text inside the quotes is read as written, so a variable name there is not substituted. Multiply an interval by a number, or build one with make_interval.

SELECT interval '1 day' * 21;

2Fix

Multiply interval '1 month' by the count, or use make_interval with the months argument.

SELECT start_date + interval '1 month' * months_count FROM subscriptions;
SELECT date '2001-03-31' + make_interval(months => 1);

3Verify

The second statement returns 2001-04-30.

SELECT date '2001-03-31' + make_interval(months => 1);

Confirmed by postgresql.org

Did this fix it?

Cause 3 of 3

The result carries a time part, or the end of month moved

1Check

date + interval returns a timestamp. A month addition keeps the day number unless the target month lacks it: March 31 plus 1 month becomes April 30, and March 31 plus 2 months becomes May 31.

SELECT date '2001-03-31' + interval '1 month';
SELECT date '2001-03-31' + interval '2 months';

2Fix

Cast the result back to date when you need a date, and plan for the end-of-month rule.

SELECT (date '2001-03-31' + interval '1 month')::date;

3Verify

The output shows only the date.

SELECT (date '2001-03-31' + interval '1 month')::date;

Confirmed by postgresql.org

Did this fix it?

How do I stop it coming back?

  • Keep the whole interval in one quoted literal.
  • Use make_interval when the count comes from data.
  • Test month-end dates, for example the 31st, in every date calculation.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.