PostgreSQL 22009 Error: Causes and Solutions Complete Guide

PostgreSQL Error 22009: Invalid Time Zone Displacement Value

PostgreSQL error code 22009 (invalid_time_zone_displacement_value) is raised when a time zone offset falls outside the valid range or uses an incorrect format. The SQL standard allows time zone displacements only between -16:00 and +16:00**, and any value outside this range—or formatted incorrectly—will immediately trigger this error. This commonly affects applications that handle user-supplied time zone data without proper validation.

---

Top 3 Causes

1. Out-of-Range Offset Value

The most frequent cause is simply providing an offset beyond the permitted -16:00 to +16:00 range.

-- This will fail
SELECT TIMESTAMPTZ '2024-01-15 10:00:00+25:00';
-- ERROR:  invalid time zone displacement value: "+25:00"
-- SQLSTATE: 22009

-- Correct usage
SELECT TIMESTAMPTZ '2024-01-15 10:00:00+09:00';

-- Even better: use IANA timezone names
SELECT NOW() AT TIME ZONE 'Asia/Seoul';
SELECT NOW() AT TIME ZONE 'America/New_York';

2. Incorrectly Formatted Timezone String

Offsets must follow the ±HH:MM or ±HH format. Strings like UTC+9, +5.5, or 9:00 will fail parsing and cause error 22009.

-- Problematic format
SELECT TIMESTAMP '2024-01-15 10:00:00' AT TIME ZONE 'UTC+9'; -- may fail or misbehave

-- Correct formats
SELECT TIMESTAMP '2024-01-15 10:00:00' AT TIME ZONE '+09:00';
SELECT TIMESTAMP '2024-01-15 10:00:00' AT TIME ZONE 'Asia/Tokyo';

-- Verify valid timezone names in your instance
SELECT name, utc_offset
FROM pg_timezone_names
WHERE name LIKE 'America/%'
ORDER BY utc_offset;

3. Unvalidated Dynamic Offset in PL/pgSQL or Application Code

When a time zone offset is passed as a parameter and injected into dynamic SQL without validation, bad input can easily trigger this error.

-- Safe dynamic timezone conversion function
CREATE OR REPLACE FUNCTION safe_tz_convert(
    p_ts     TIMESTAMP,
    p_offset TEXT
) RETURNS TIMESTAMPTZ AS $$
DECLARE
    v_result TIMESTAMPTZ;
BEGIN
    -- Validate format with regex
    IF p_offset !~ '^[+-][0-9]{2}:[0-9]{2}$' THEN
        RAISE EXCEPTION 'Invalid offset format: %. Use ±HH:MM.', p_offset;
    END IF;

    -- Validate range
    IF ABS(SPLIT_PART(p_offset, ':', 1)::INTEGER) > 16 THEN
        RAISE EXCEPTION 'Offset % is out of allowed range (-16:00 to +16:00).', p_offset;
    END IF;

    v_result := p_ts AT TIME ZONE p_offset;
    RETURN v_result;

EXCEPTION
    WHEN SQLSTATE '22009' THEN
        RAISE EXCEPTION 'Timezone conversion failed for offset: %', p_offset;
END;
$$ LANGUAGE plpgsql;

-- Usage
SELECT safe_tz_convert('2024-06-01 09:00:00', '+09:00'); -- OK
SELECT safe_tz_convert('2024-06-01 09:00:00', '+25:00'); -- Caught and handled

---

Quick Fix Solutions

-- Replace numeric offsets with IANA timezone names wherever possible
-- Before:
SELECT created_at AT TIME ZONE '+09:00' FROM orders;

-- After (preferred):
SELECT created_at AT TIME ZONE 'Asia/Seoul' FROM orders;

-- Find rows with invalid offsets stored in a column
SELECT id, tz_offset
FROM user_settings
WHERE tz_offset !~ '^[+-][0-1][0-9]:[0-5][0-9]$';

-- Catch 22009 in a DO block for batch processing
DO $$
DECLARE
    v_ts TIMESTAMPTZ;
BEGIN
    BEGIN
        v_ts := TIMESTAMP '2024-01-01 00:00:00' AT TIME ZONE '+99:00';
    EXCEPTION
        WHEN SQLSTATE '22009' THEN
            RAISE NOTICE 'Invalid timezone offset caught. Using UTC as fallback.';
            v_ts := TIMESTAMP '2024-01-01 00:00:00' AT TIME ZONE 'UTC';
    END;
    RAISE NOTICE 'Result: %', v_ts;
END;
$$;

---

Prevention Tips

1. Prefer IANA Timezone Names Over Numeric Offsets

IANA names like Asia/Seoul or Europe/Berlin are immune to displacement range errors and also handle daylight saving time automatically. Maintain a whitelist validated against pg_timezone_names.

-- Whitelist check
SELECT COUNT(*) > 0 AS is_valid
FROM pg_timezone_names
WHERE name = 'Asia/Seoul';

2. Enforce Constraints at the Database Level

Use a CHECK constraint or a custom DOMAIN type to block invalid offsets before they are ever stored.

-- Domain type for safe offset storage
CREATE DOMAIN tz_offset_type AS TEXT
    CHECK (VALUE ~ '^[+-][0-1][0-9]:[0-5][0-9]$');

CREATE TABLE sessions (
    session_id  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_tz     tz_offset_type NOT NULL DEFAULT '+00:00'
);

-- Valid insert
INSERT INTO sessions (user_tz) VALUES ('+05:30');

-- This will be rejected at the constraint level
INSERT INTO sessions (user_tz) VALUES ('+25:00');

---

Related Errors

Code	Name	Description
22007	invalid_datetime_format	Malformed date/time literal string
22008	datetime_field_overflow	Date/time field value out of valid range
22P02	invalid_text_representation	Failed cast from text to a date/time type

---

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