DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 22009 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 22009: invalid time zone displacement value

PostgreSQL error code 22009 occurs when a time zone offset (displacement) value is outside the permitted range or has an invalid format. This error is triggered during timestamp parsing, AT TIME ZONE operations, or when inserting TIMESTAMPTZ values with malformed UTC offsets. The valid offset range in PostgreSQL is -15:59:59 to +15:59:59.

Top 3 Causes

1. UTC Offset Out of Allowed Range

PostgreSQL strictly enforces offset boundaries. Any offset beyond ±15:59 will immediately raise 22009.

-- Reproducing the error
SELECT TIMESTAMPTZ '2024-01-15 12:00:00+16:00';
-- ERROR:  invalid time zone displacement value: "+16:00"
-- SQLSTATE: 22009

-- Valid offsets within range work fine
SELECT TIMESTAMPTZ '2024-01-15 12:00:00+09:00';  -- OK
SELECT TIMESTAMPTZ '2024-01-15 12:00:00-05:30';  -- OK
Enter fullscreen mode Exit fullscreen mode

2. Malformed Offset String Format

An offset with minutes >= 60 or incorrect formatting will also trigger this error.

-- Invalid minute value in offset
SELECT TIMESTAMPTZ '2024-01-15 12:00:00+05:75';
-- ERROR: invalid time zone displacement value: "+05:75"

-- Incorrect format without colon separator
SELECT '2024-01-15 12:00:00+0900'::TIMESTAMPTZ;
-- This may fail depending on PostgreSQL version and context

-- Correct format
SELECT '2024-01-15 12:00:00+09:00'::TIMESTAMPTZ;  -- OK
Enter fullscreen mode Exit fullscreen mode

3. Dynamic Offset Injection from Application Code

Applications (especially JavaScript) often calculate timezone offsets in minutes and inject them dynamically into SQL. JavaScript's getTimezoneOffset() returns the offset with an inverted sign, causing incorrect SQL strings.

-- Simulating a bad dynamic offset passed from application
DO $$
DECLARE
    v_offset TEXT := '+99:00';  -- bad value from app logic
    v_ts     TIMESTAMPTZ;
BEGIN
    -- This will raise 22009
    EXECUTE format('SELECT TIMESTAMPTZ %L', '2024-01-15 12:00:00' || v_offset)
    INTO v_ts;
EXCEPTION
    WHEN SQLSTATE '22009' THEN
        RAISE WARNING 'Caught 22009: invalid offset -> %', v_offset;
END;
$$;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Safe parsing wrapper function:

CREATE OR REPLACE FUNCTION safe_to_timestamptz(p_input TEXT)
RETURNS TIMESTAMPTZ AS $$
BEGIN
    RETURN p_input::TIMESTAMPTZ;
EXCEPTION
    WHEN SQLSTATE '22009' THEN
        RAISE WARNING '[22009] Invalid TZ offset in: %', p_input;
        RETURN NULL;
    WHEN invalid_datetime_format THEN
        RAISE WARNING '[22007] Bad datetime format: %', p_input;
        RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Usage
SELECT safe_to_timestamptz('2024-01-15 12:00:00+16:00');  -- returns NULL
SELECT safe_to_timestamptz('2024-01-15 12:00:00+09:00');  -- returns valid TIMESTAMPTZ
Enter fullscreen mode Exit fullscreen mode

Normalize offset from integer minutes:

CREATE OR REPLACE FUNCTION minutes_to_tz_offset(p_minutes INT)
RETURNS TEXT AS $$
BEGIN
    IF p_minutes < -959 OR p_minutes > 959 THEN
        RAISE EXCEPTION 'Offset out of range: % minutes', p_minutes
            USING ERRCODE = '22009';
    END IF;
    RETURN format('%s%02d:%02d',
        CASE WHEN p_minutes >= 0 THEN '+' ELSE '-' END,
        ABS(p_minutes) / 60,
        ABS(p_minutes) % 60
    );
END;
$$ LANGUAGE plpgsql;

-- Convert JS getTimezoneOffset() result (-540 = UTC+9)
SELECT minutes_to_tz_offset(-540);  -- returns '+09:00'
SELECT minutes_to_tz_offset(330);   -- returns '-05:30'
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Validate offsets at the database level using a domain type:

-- Create a domain that enforces valid offset format
CREATE DOMAIN valid_tz_offset AS TEXT
    CHECK (VALUE ~ '^[+-](0[0-9]|1[0-5]):[0-5][0-9]$');

-- Apply to tables that receive external timestamp data
CREATE TABLE api_events (
    id         SERIAL PRIMARY KEY,
    event_time TIMESTAMPTZ NOT NULL,
    tz_offset  valid_tz_offset
);

-- Invalid offset is blocked at DB level
INSERT INTO api_events (event_time, tz_offset)
VALUES (NOW(), '+16:00');
-- ERROR: value for domain valid_tz_offset violates check constraint
Enter fullscreen mode Exit fullscreen mode

2. Always handle SQLSTATE '22009' explicitly in PL/pgSQL procedures that process external or user-supplied timestamp data. Log the offending input to an error table for post-incident analysis, and never let unvalidated offset strings reach a TIMESTAMPTZ cast without a safety net. Pair this with application-side regex validation (^[+-](0[0-9]|1[0-5]):[0-5][0-9]$) before submitting queries to the database.

Related Errors

Code Name When it occurs
22007 invalid_datetime_format Entire datetime string format is unparseable
22008 datetime_field_overflow Individual fields (month, day, hour) out of range
22023 invalid_parameter_value Wrong argument type passed to time zone functions

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