PostgreSQL 22008 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 22008: datetime field overflow

PostgreSQL error code 22008 (datetime_field_overflow) occurs when a date/time value or the result of a datetime operation exceeds the valid range supported by PostgreSQL. This typically happens during timezone conversions, interval arithmetic, or when inserting out-of-range values — and can be surprisingly tricky to debug in production systems handling global timezones or legacy data.

Top 3 Causes & Fixes

1. Timezone Conversion Pushes Value Out of Range

When applying AT TIME ZONE to a timestamp already near the boundary, the internal UTC conversion can overflow.

-- This will cause ERROR 22008
SELECT '9999-12-31 23:59:59'::timestamp AT TIME ZONE 'UTC+14';

-- Fix: Use a safe wrapper function with exception handling
CREATE OR REPLACE FUNCTION safe_tz_convert(p_ts TIMESTAMP, p_tz TEXT)
RETURNS TIMESTAMPTZ AS $$
BEGIN
  RETURN p_ts AT TIME ZONE p_tz;
EXCEPTION
  WHEN datetime_field_overflow THEN
    RAISE WARNING 'Overflow for: %', p_ts;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Usage
SELECT safe_tz_convert('9999-12-31 23:59:59'::timestamp, 'Asia/Tokyo');

2. Out-of-Range Values Inserted Directly

Inserting values like '0000-00-00' or timestamps beyond PostgreSQL's supported range (4713 BC to 5874897 AD) will trigger this error.

-- This causes ERROR 22008
INSERT INTO events (event_at) VALUES ('9999-12-31 23:59:59+14:00');

-- Fix: Add a CHECK constraint to block invalid values at the table level
CREATE TABLE events (
  id       SERIAL PRIMARY KEY,
  event_at TIMESTAMPTZ NOT NULL,
  CONSTRAINT chk_event_at_range
    CHECK (
      event_at >= '0001-01-01 00:00:00+00'::timestamptz AND
      event_at <= '9999-12-31 23:59:59+00'::timestamptz
    )
);

-- Fix: Use a safe cast function for batch processing
CREATE OR REPLACE FUNCTION safe_cast_timestamptz(p_input TEXT)
RETURNS TIMESTAMPTZ AS $$
BEGIN
  RETURN p_input::TIMESTAMPTZ;
EXCEPTION
  WHEN datetime_field_overflow THEN RETURN NULL;
  WHEN invalid_datetime_format THEN RETURN NULL;
END;
$$ LANGUAGE plpgsql;

SELECT safe_cast_timestamptz('9999-12-31 23:59:59+14');
-- Returns NULL instead of throwing an error

3. INTERVAL Arithmetic Overflow

Adding or subtracting large intervals to boundary dates causes the result to exceed the allowed range.

-- This causes ERROR 22008
SELECT '9999-01-01'::date + INTERVAL '999 years';

-- Fix: Cap the result using LEAST/GREATEST
SELECT LEAST(
  '9999-01-01'::date + INTERVAL '999 years',
  '9999-12-31'::date
) AS safe_date;

-- Fix: Safe interval addition function
CREATE OR REPLACE FUNCTION safe_date_add(p_date DATE, p_interval INTERVAL)
RETURNS DATE AS $$
BEGIN
  RETURN p_date + p_interval;
EXCEPTION
  WHEN datetime_field_overflow THEN
    RETURN '9999-12-31'::DATE; -- return max or NULL per your business logic
END;
$$ LANGUAGE plpgsql;

SELECT safe_date_add('9999-06-01'::DATE, INTERVAL '500 years');

Quick Prevention Tips

Store everything in UTC. Set your database and application timezone to UTC and only convert for display purposes. This eliminates most timezone-induced overflow scenarios.

-- Apply UTC at database level (add to postgresql.conf or run once)
ALTER DATABASE mydb SET timezone TO 'UTC';
SHOW timezone;

Validate at the boundary. Always include CHECK constraints on datetime columns and add boundary-value test cases (min date, max date, extreme timezone offsets) to your CI/CD pipeline so overflows are caught before reaching production.

Related Errors

Code	Name	Notes
`22007`	invalid_datetime_format	Wrong format string, often appears alongside 22008
`22003`	numeric_value_out_of_range	Can occur during EPOCH/numeric conversions
`22P02`	invalid_text_representation	Bad string-to-date CAST, common in batch imports

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