DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 22008 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 22008: datetime field overflow

PostgreSQL error 22008 (datetime field overflow) occurs when a date or time value exceeds the valid range supported by its data type, or when a datetime arithmetic operation produces an out-of-range result. This commonly surfaces during data migrations, when processing external API payloads, or when performing unchecked interval calculations in application logic.

Top 3 Causes

1. Inserting Out-of-Range or Invalid Date Values

The most common culprit is inserting date strings that fall outside PostgreSQL's supported range, especially when migrating from MySQL where 0000-00-00 is a valid (though questionable) date value.

-- This will throw error 22008
INSERT INTO orders (created_at) VALUES ('0000-00-00 00:00:00');
-- ERROR:  date/time field value out of range: "0000-00-00 00:00:00"

-- Fix: Use a safe conversion function
CREATE OR REPLACE FUNCTION safe_to_timestamp(p_input TEXT)
RETURNS TIMESTAMP AS $$
BEGIN
  RETURN p_input::timestamp;
EXCEPTION
  WHEN datetime_field_overflow THEN RETURN NULL;
  WHEN invalid_datetime_format THEN RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Usage
SELECT safe_to_timestamp('0000-00-00');      -- returns NULL
SELECT safe_to_timestamp('2024-08-15 10:00'); -- returns valid timestamp
Enter fullscreen mode Exit fullscreen mode

2. Interval Arithmetic Exceeding Timestamp Bounds

Adding or subtracting large INTERVAL values can push a timestamp beyond PostgreSQL's maximum (294276 AD) or minimum (4713 BC) representable value.

-- This will overflow
SELECT NOW() + INTERVAL '999999999 years';
-- ERROR:  timestamp out of range

-- Fix: Clamp the result using LEAST/GREATEST
SELECT LEAST(
  NOW() + (user_input_years || ' years')::interval,
  '9999-12-31 23:59:59'::timestamp
) AS safe_expiry
FROM (SELECT 500 AS user_input_years) t;

-- Safe subscription expiry update
UPDATE subscriptions
SET expires_at = LEAST(
  started_at + (plan_months || ' months')::interval,
  '9999-12-31 23:59:59'::timestamp
)
WHERE user_id = 101;
Enter fullscreen mode Exit fullscreen mode

3. Time Zone Conversion Hitting Non-Existent Local Times

During Daylight Saving Time (DST) transitions, certain local times simply do not exist (e.g., clocks skip from 2:00 AM to 3:00 AM). Attempting to convert such a timestamp can trigger overflow or ambiguity errors.

-- Risky: referencing a DST gap time
SELECT '2024-03-10 02:30:00'::timestamp AT TIME ZONE 'America/New_York';

-- Fix: Always store in UTC using TIMESTAMPTZ
CREATE TABLE events (
  id         SERIAL PRIMARY KEY,
  event_name TEXT,
  event_time TIMESTAMPTZ  -- stored as UTC, displayed per timezone
);

-- Insert using explicit UTC offset
INSERT INTO events (event_name, event_time)
VALUES ('webinar', '2024-03-10 07:30:00+00');

-- Display in local timezone at query time
SELECT
  event_name,
  event_time AT TIME ZONE 'America/New_York' AS local_time
FROM events;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Scenario Solution
MySQL migration with 0000-00-00 Use safe_to_timestamp() wrapper or CASE to substitute NULL
Large interval addition Clamp with LEAST() / GREATEST()
DST-related overflow Store all datetimes as TIMESTAMPTZ (UTC)
Bulk import with unknown quality Wrap in exception-handling PL/pgSQL block

Prevention Tips

1. Enforce Boundaries with CHECK Constraints and Custom Domains

Define acceptable date ranges at the schema level so bad data never reaches your tables.

-- Custom domain with built-in validation
CREATE DOMAIN business_date AS DATE
  CHECK (VALUE BETWEEN '1970-01-01' AND '2099-12-31');

CREATE TABLE contracts (
  id         SERIAL PRIMARY KEY,
  start_date business_date NOT NULL,
  end_date   business_date NOT NULL,
  CHECK (end_date >= start_date)
);
Enter fullscreen mode Exit fullscreen mode

2. Standardize on UTC Storage and Keep tzdata Current

Always store timestamps as TIMESTAMPTZ (UTC internally) and convert to local time only at the presentation layer. Additionally, keep your system and PostgreSQL tzdata up to date to avoid DST-related surprises.

-- Verify current timezone data for a region
SELECT name, utc_offset, is_dst
FROM pg_timezone_names
WHERE name = 'America/New_York';

-- Confirm your session is working in UTC
SET timezone = 'UTC';
SHOW timezone;
Enter fullscreen mode Exit fullscreen mode

Related Errors

  • 22007 – invalid_datetime_format: Malformed date string before even checking range; often appears alongside 22008 during bulk imports.
  • 22009 – invalid_time_zone_displacement_value: Invalid timezone offset, commonly paired with AT TIME ZONE issues.
  • 22003 – numeric_value_out_of_range: The numeric equivalent of 22008; handle both together in input validation routines.

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