DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 22015 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 22015: interval field overflow

PostgreSQL error code 22015 (interval_field_overflow) is raised when an INTERVAL value exceeds the internal storage limits of PostgreSQL's INTERVAL type, which stores values in microseconds with a theoretical range of approximately ±178,000,000 years. Although the range is enormous, certain arithmetic operations, bad string casts, or precision constraints can easily trigger this error in production. Understanding its root causes is the fastest way to fix it and keep your pipelines stable.

Top 3 Causes and Fixes

1. Arithmetic Operations Producing Out-of-Range Results

Subtracting two timestamps that are astronomically far apart can produce an INTERVAL that exceeds PostgreSQL's internal microsecond limit.

-- This triggers error 22015
SELECT '4714-11-24 BC'::timestamp - '9999-12-31 23:59:59'::timestamp;
-- ERROR:  interval field overflow

-- Fix: Use EXTRACT(EPOCH ...) to get numeric seconds instead
SELECT EXTRACT(EPOCH FROM '9999-12-31'::timestamp)
     - EXTRACT(EPOCH FROM '0001-01-01'::timestamp) AS diff_seconds;

-- Fix: Guard with a safe range filter before subtracting
SELECT end_ts - start_ts AS duration
FROM events
WHERE start_ts BETWEEN '0001-01-01' AND '9999-12-31'
  AND end_ts   BETWEEN '0001-01-01' AND '9999-12-31';
Enter fullscreen mode Exit fullscreen mode

2. Casting an Oversized String to INTERVAL

When converting external data (ETL, APIs, CSV files) directly to INTERVAL without validation, an oversized numeric value in the string will cause overflow.

-- Triggers error 22015
SELECT '999999999 years'::interval;
-- ERROR:  interval field overflow

-- Fix: Wrap with a safe conversion function
CREATE OR REPLACE FUNCTION safe_to_interval(p_text TEXT)
RETURNS INTERVAL LANGUAGE plpgsql AS $$
BEGIN
  RETURN p_text::interval;
EXCEPTION
  WHEN interval_field_overflow THEN
    RAISE WARNING 'Overflow for input: %', p_text;
    RETURN NULL;
END;
$$;

SELECT safe_to_interval('999999999 years');  -- returns NULL with WARNING
SELECT safe_to_interval('3 days 4 hours');   -- returns valid interval

-- Fix: Use make_interval() for programmatic construction
SELECT make_interval(years => 500, days => 120, hours => 8);
Enter fullscreen mode Exit fullscreen mode

3. INTERVAL Column Precision Constraints

When a column is defined with a restricted INTERVAL precision (e.g., INTERVAL MINUTE(2)), inserting a value larger than the precision allows causes overflow.

-- Column defined with precision that's too tight
CREATE TABLE jobs (
  id SERIAL PRIMARY KEY,
  run_duration INTERVAL MINUTE(2)  -- allows only up to 99 minutes
);

INSERT INTO jobs (run_duration) VALUES ('500 minutes');
-- ERROR:  interval field overflow

-- Fix: Relax the precision constraint
ALTER TABLE jobs
  ALTER COLUMN run_duration TYPE INTERVAL;

-- Fix: Or specify a wider precision
ALTER TABLE jobs
  ALTER COLUMN run_duration TYPE INTERVAL MINUTE(6);

-- Check current column definition
SELECT column_name, interval_type, interval_precision
FROM information_schema.columns
WHERE table_name = 'jobs';
Enter fullscreen mode Exit fullscreen mode

Quick Prevention Tips

Validate before you insert. Add a CHECK constraint or a BEFORE trigger to reject values outside your acceptable business range before they hit the INTERVAL column.

ALTER TABLE jobs
  ADD CONSTRAINT chk_run_duration
  CHECK (run_duration >= INTERVAL '0' AND run_duration <= INTERVAL '24 hours');
Enter fullscreen mode Exit fullscreen mode

Store large durations as BIGINT seconds. If your system deals with very wide time ranges, store durations as plain BIGINT (seconds or milliseconds) and convert only when needed. This eliminates overflow risk entirely.

-- Store as seconds
CREATE TABLE tasks (
  id SERIAL PRIMARY KEY,
  duration_sec BIGINT
);

-- Convert on read
SELECT id, make_interval(secs => duration_sec) AS duration
FROM tasks;
Enter fullscreen mode Exit fullscreen mode

Related Errors

Code Name Notes
22003 numeric_value_out_of_range Similar overflow for numeric types
22008 datetime_field_overflow DATE/TIMESTAMP range exceeded
22007 invalid_datetime_format Bad string format during date/interval cast

Keeping these three errors in mind alongside 22015 will help you build more robust date and time handling across your entire PostgreSQL schema.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)