invalid time zone displacement value

Category: Data ExceptionVersions: All Postgres versions

What this means

SQLSTATE 22009 is raised when a time zone offset or name specified in a timestamp or time value is not a valid time zone displacement. Valid offsets are in the range -15:59 to +15:59.

Why it happens

1Casting a timestamptz string with an invalid timezone offset (e.g., +25:00)
2Using a timezone name that does not exist in the pg_timezone_names catalogue
3Supplying an out-of-range numeric UTC offset

How to reproduce

Parsing a timestamp string with an invalid timezone offset.

trigger — this will ERROR

SELECT '2024-01-01 12:00:00+25:00'::timestamptz;

ERROR: time zone displacement out of range: "+25:00"

Fix 1: Use a valid timezone name or offset

When parsing or constructing timestamptz values.

fix

SELECT '2024-01-01 12:00:00+05:30'::timestamptz; -- valid IST offset

Why this works

UTC offsets must be in [-15:59, +15:59]. Use a timezone name from pg_timezone_names for named zones.

Fix 2: Validate input timezone strings before casting

When accepting timezone data from external sources.

fix

SELECT name FROM pg_timezone_names WHERE name = :tz_input;

Why this works

Checking pg_timezone_names confirms the timezone exists before using it in a cast.

Sources

📚 Official docs: https://www.postgresql.org/docs/current/errcodes-appendix.html

🔧 Source ref: Class 22 — Data Exception

Confidence assessment

✅ HIGH confidence

Standard SQLSTATE for timezone validation. Stable across versions.