Menu

PostgreSQL Error 22009: Invalid Time Zone Displacement

Troubleshoot PostgreSQL SQLSTATE 22009 by checking numeric UTC offsets, time-zone input, and whether the value needs a date-aware type.

Posted on By
On this page

PostgreSQL SQLSTATE 22009 is invalid_time_zone_displacement_value. It indicates that a numeric time-zone offset is outside the range PostgreSQL accepts. The error-code appendix distinguishes it from 22008 (datetime_field_overflow) and 22007 (invalid_datetime_format). See the PostgreSQL error-code appendix.

Check the numeric offset

For time with time zone, PostgreSQL documents offsets from -15:59 through +15:59. An offset such as +16:00 is out of range:

SELECT TIME WITH TIME ZONE '12:00:00+16:00';

Check how the application or import builds the offset. Look for an incorrect hour, minute, sign, or unit conversion; do not change the database type to hide an invalid offset. PostgreSQL’s date/time type documentation lists the supported ranges and input forms.

Choose a type that matches the value

A time of day and a timestamp represent different information. time with time zone stores a time and a fixed UTC offset but has no date for daylight-saving rules. PostgreSQL advises against using this type for most applications. If the value represents an actual moment, store a timestamp with time zone and provide the timestamp with the intended zone or offset. If it represents local wall-clock time with no zone, use time or timestamp without time zone and do not append an offset.

When the source represents a region, prefer a named IANA zone such as America/New_York with a date-aware timestamp, rather than saving a fixed offset as if it captured future daylight-saving changes. See PostgreSQL’s guidance on date/time types and time-zone conversions.

  • 22009 (invalid_time_zone_displacement_value): a numeric time-zone offset is outside the accepted range.
  • 22008 (datetime_field_overflow): a date or time field is outside its valid range; see Error 22008 troubleshooting.
  • 22007 (invalid_datetime_format): the input does not match an accepted date/time format; see Error 22007 troubleshooting.

For other SQLSTATE guides, browse PostgreSQL Error Troubleshooting.