PostgreSQL Invalid Time Zone Displacement Value (SQLSTATE 22009)

PostgreSQL raises ERROR: invalid input syntax for type timestamp with time zone or ERROR: time zone displacement out of range with SQLSTATE 22009 and condition name invalid_time_zone_displacement_value when a numeric UTC offset (a "displacement") supplied to a timestamp or interval operation is outside the valid range of −15:59 to +15:59.

What This Error Means

SQLSTATE 22009 belongs to class 22 — Data Exception — the same class that covers errors such as division by zero (22012) and invalid datetime format (22007). A time zone displacement is a signed numeric offset from UTC expressed as ±HH:MM (e.g., +05:30, -08:00). It differs from a named time zone (e.g., America/New_York): named zones are looked up in the IANA time zone database, whereas a displacement is a raw offset supplied directly in a string literal or cast expression.

PostgreSQL accepts displacement values in the range −15:59 to +15:59 (the extremes correspond to Etc/GMT±15 in the IANA database and cover every real-world time zone). Any offset outside this window is mathematically defined but has no geographic meaning, and PostgreSQL refuses it with 22009 to prevent silent data corruption.

When this error is thrown, the current SQL statement is aborted and the transaction is rolled back to the state it was in before that statement. If you are inside an explicit transaction block, the transaction enters the aborted state and you must issue ROLLBACK (or ROLLBACK TO SAVEPOINT) before running any further statements.

Common Causes

1. Hardcoded out-of-range offset in a string literal — casting a string such as '2024-01-01 00:00:00+99:00' or '2024-01-01 00:00:00-16:00' to timestamptz directly embeds a displacement value that PostgreSQL rejects.

2. Dynamic offset built from user input or calculation — application code that computes an offset (e.g., minutes east of UTC) and formats it as a ±HH:MM string can produce a value outside ±15:59 if the input is untrusted or the arithmetic is wrong (e.g., treating a Unix offset in seconds as minutes).

3. Importing data from external systems — ETL pipelines or CSV imports that originate from systems using non-standard or sentinel offset values (such as +99:00 as a placeholder for "UTC unknown") will trigger this error when PostgreSQL tries to parse the column.

4. Using AT TIME ZONE with a numeric interval outside range — constructing an interval expression like INTERVAL '+16 hours' and passing it to AT TIME ZONE can hit this limit, though more commonly this manifests with explicit string offsets.

How to Fix invalid_time_zone_displacement_value

1. Validate the offset before casting. If you control the input, clamp or reject offset values outside the −15:59 to +15:59 range in application code before sending the query to PostgreSQL.

2. Replace sentinel offset values with NULL or a known valid offset. If +99:00 is used as a placeholder meaning "unknown timezone," convert it before import:

-- Replace invalid sentinel with NULL during import
UPDATE staging_table
SET event_time = NULL
WHERE event_time_raw LIKE '%+99:%';

3. Use a named time zone instead of a numeric offset where possible. Named zones are more readable and eliminate manual offset arithmetic:

-- Instead of a hardcoded offset
SELECT '2024-06-01 12:00:00+05:30'::timestamptz;

-- Use a named zone with AT TIME ZONE
SELECT ('2024-06-01 12:00:00'::timestamp) AT TIME ZONE 'Asia/Kolkata';

4. Sanitize computed offsets from integers. When converting an integer (e.g., minutes from UTC) to a displacement string, guard against out-of-range values:

-- Build a safe offset string from an integer minutes value
-- Returns NULL for out-of-range values rather than raising an error
SELECT CASE
    WHEN abs(offset_minutes) <= 959  -- ±15h 59m = 959 minutes
    THEN to_timestamp(0) AT TIME ZONE
         (make_interval(mins => offset_minutes)::text)
    ELSE NULL
END
FROM your_table;

5. Catch and handle the error in PL/pgSQL if you need graceful degradation:

DO $$
BEGIN
    PERFORM ('2024-01-01 00:00:00+99:00'::timestamptz);
EXCEPTION
    WHEN invalid_time_zone_displacement_value THEN
        RAISE NOTICE 'Invalid timezone offset — skipping row';
END;
$$;

Additional Information

• SQLSTATE 22009 has been part of the SQL standard and PostgreSQL for many years; no specific PostgreSQL version introduced a change in behavior around this error in recent releases.
• Related SQLSTATE codes in class 22 that often appear alongside timezone handling issues:
  • 22007 — invalid_datetime_format: the timestamp string itself is malformed.
  • 22008 — datetime_field_overflow: a valid timestamp value falls outside the storable range (year 0001–9999 in PostgreSQL).
• JDBC drivers surface this as a PSQLException with getSQLState() returning "22009". Most ORM frameworks (Hibernate, SQLAlchemy, ActiveRecord) will rethrow it as a database-level exception with the original message preserved.
• This error only fires when PostgreSQL is actively parsing a displacement from a string. If your application uses timestamptz parameters bound via a driver (e.g., passing a java.time.OffsetDateTime or Python datetime with tzinfo), the driver handles offset encoding and you are unlikely to see 22009 unless the driver itself has a bug.

Frequently Asked Questions

Why does PostgreSQL limit offsets to ±15:59 when UTC+16 exists? Some Pacific island territories (e.g., Tokelau, Samoa) use offsets beyond UTC+12, reaching UTC+13 and UTC+14. PostgreSQL's limit of ±15:59 accommodates these plus a small margin. UTC+16 and beyond do not correspond to any real jurisdiction and are not valid displacements in PostgreSQL's model.

Does this error also apply to time with time zone columns? Yes. PostgreSQL's timetz type also stores a displacement, and the same ±15:59 limit applies. In practice timetz is rarely used (the PostgreSQL documentation itself cautions against it), but the error can appear there as well.

Can I disable this validation to allow arbitrary offsets? No. The ±15:59 limit is enforced by the SQL standard and is hardcoded in PostgreSQL's datetime parsing logic. There is no GUC parameter to relax it. The correct approach is to sanitize input before it reaches the database.

How do I reproduce this error to test my error handler?

SELECT '2024-01-01 00:00:00+16:00'::timestamptz;
-- ERROR:  time zone displacement out of range: "+16:00"
-- SQLSTATE: 22009