PostgreSQL Interval Field Overflow (SQLSTATE 22015)

When a date/time computation produces an interval value that exceeds PostgreSQL's internal storage limits, the server raises:

ERROR:  interval field value out of range: "..."
SQLSTATE: 22015

The condition name is interval_field_overflow, and it belongs to error class 22 — Data Exception. This class covers errors where a value is well-formed SQL but cannot be represented in the target type.

What This Error Means

PostgreSQL stores interval values using three separate 32-bit fields: months (for years and months), days, and microseconds (for hours, minutes, seconds, and sub-seconds). The microseconds component is a 64-bit integer, giving a maximum of roughly ±178,000,000 years of total span, but each field has its own independent overflow boundary. An operation that produces an intermediate or final value exceeding any one of these bounds triggers SQLSTATE 22015.

The error is raised at execution time, not at parse or plan time, because the overflow depends on actual data values rather than query structure. When it fires, PostgreSQL immediately aborts the current statement and rolls back any work done within it. If the statement ran inside an explicit transaction, the entire transaction enters an aborted state and must be rolled back before any further work can be done on that connection.

This error is uncommon in normal application use because PostgreSQL's interval range is extremely wide. It most often surfaces in data pipelines that aggregate or accumulate intervals, in dynamically-constructed interval strings from untrusted input, or in edge-case arithmetic involving very large epoch values.

Common Causes

1. Summing or accumulating intervals over a large dataset. Using SUM() on an interval column across millions of rows can exceed the microseconds field's range if the individual intervals are large (e.g., summing millions of rows each containing '1 year').

2. Multiplying an interval by a very large number. The expression INTERVAL '1 second' * 9999999999999 will overflow the microseconds field. This can happen when a numeric multiplier comes from user input or an unbounded column value.

3. Constructing an interval from an out-of-range string. Passing a computed string like '999999999999 years' to INTERVAL or to make_interval() will trigger the overflow if the value exceeds the months field's capacity.

4. Subtracting very distant timestamps. The difference between two timestamp or timestamptz values is returned as an interval. If the timestamps are far enough apart (well beyond normal calendar ranges), the resulting interval can overflow.

5. Using age() or date_part() with extreme dates. Functions that internally compute intervals may overflow when given dates like '4713-01-01 BC' or '294276-01-01 AD' combined with arithmetic that compounds the span.

How to Fix interval_field_overflow

1. Validate multipliers before applying them. If an interval is multiplied by a user-supplied or computed number, clamp or validate the value before the multiplication:

   -- Clamp the multiplier to a safe range before use
   SELECT INTERVAL '1 second' * LEAST(multiplier, 315360000)  -- 10 years in seconds
   FROM your_table;
   

2. Use numeric or bigint seconds instead of interval for large aggregations. For SUM-style aggregations, accumulate in a numeric type and convert only at the end:

   -- Instead of SUM(duration_interval), aggregate as seconds first
   SELECT make_interval(secs => SUM(EXTRACT(EPOCH FROM duration_interval)))
   FROM your_table;
   

3. Catch and handle the error in application code. If overflow is a genuine edge case rather than a bug, catch SQLSTATE 22015 in your driver and substitute a sentinel value or NULL:

   DO $$
   BEGIN
     PERFORM INTERVAL '1 second' * 99999999999999;
   EXCEPTION
     WHEN interval_field_overflow THEN
       RAISE NOTICE 'Interval overflow caught, using NULL';
   END;
   $$;
   

4. Sanitize interval strings from external input. When building an interval literal from user-supplied data, parse and range-check the numeric components before passing them to PostgreSQL:

   -- Use make_interval() with validated integer arguments instead of
   -- concatenating a raw string into an INTERVAL cast
   SELECT make_interval(years => LEAST(user_years::int, 10000));
   

5. Break large timestamp subtractions into bounded segments. If you need the difference between extreme dates, compute the difference in years using EXTRACT arithmetic rather than a single interval subtraction.

Additional Information

• SQLSTATE 22015 is part of error class 22 (Data Exception), the same class as numeric_value_out_of_range (22003), datetime_field_overflow (22008), and invalid_datetime_format (22007). These are all value-level data errors, not schema or permission errors.
• The error has existed since early PostgreSQL versions; the internal interval storage format has not changed significantly since PostgreSQL 7.x.
• PostgreSQL's maximum interval is approximately ±178,000,000 years, but per-field limits are lower: the months field is a 32-bit integer (roughly ±178 million months ≈ ±14.8 million years), and the microseconds field is 64-bit (roughly ±292 years when expressed as raw microseconds).
• Most PostgreSQL drivers (libpq, psycopg2, asyncpg, JDBC) surface this as a generic DatabaseError or DataError with SQLSTATE 22015 in the exception's pgcode attribute. Check the driver's exception hierarchy to catch it specifically without catching unrelated data errors.
• ORMs typically bubble this up as an unhandled server error; there is no ORM-level mitigation. Add a guard at the data layer.

Frequently Asked Questions

Why does this error only appear at runtime, not when the query is compiled? PostgreSQL validates interval overflow based on actual values, not on the query's syntactic structure. The planner has no way to know that a multiplier column will contain 9999999999 until the row is actually processed. Overflow checks happen during expression evaluation, so the error fires row-by-row and may appear after partial work has already been done.

Can I reproduce this error intentionally to test error handling? Yes. A simple and reliable way to trigger SQLSTATE 22015 is:

SELECT INTERVAL '1 microsecond' * 9223372036854775807;

This multiplies by INT8_MAX, which will always overflow the microseconds field.

Is SQLSTATE 22015 the same as datetime_field_overflow (22008)? No. datetime_field_overflow (22008) refers to an out-of-range value in a date, time, or timestamp field — for example, a month value of 13 or an hour value of 25. interval_field_overflow (22015) is specific to the interval type's magnitude limits, not to field semantics.

Does this error leave my transaction open or does PostgreSQL close the connection? PostgreSQL does not close the connection. The current statement is aborted and, if you are inside an explicit BEGIN block, the transaction enters the aborted state (all subsequent commands will fail with ERROR: current transaction is aborted). Issue a ROLLBACK to clear the error state and reuse the connection normally.