SQLSTATE 22007 ERROR Class 22: Data Exception

invalid_datetime_format invalid input syntax for type interval: “…” — 22007

PostgreSQL error "invalid input syntax for type interval: "…"" (SQLSTATE 22007): what it means, common causes, and how to fix it.

PG 12, 13, 14, 15, 16, 17, 18 Official docs
Last reviewed May 2025 Grounded in source

Diagnostic Queries

Symptoms

A string given where an interval was expected does not parse as a valid interval. PostgreSQL raises SQLSTATE 22007 (invalid_datetime_format).

  • Malformed interval text or unrecognized units.
  • Common with unsupported formats or typos in unit names.
  • The message echoes the offending value.

What the server log shows

ERROR:  invalid input syntax for type interval: "3 fortnights"

Why PostgreSQL raises this — what the manual says

Section 8.5.4 Interval Input:

“Quantities of days, hours, minutes, and seconds can be specified without explicit unit markings.”

The interval input function recognizes specific unit keywords and formats. Unknown units or malformed text cannot be interpreted and are rejected with 22007.

Common causes

  • Unsupported unit words (e.g. fortnights).
  • Malformed quantity/unit pairs.
  • An empty string instead of NULL.

How to fix it

  1. Use supported units (e.g. '21 days', '2 hours 30 minutes').
  2. Use ISO 8601 interval syntax if preferred (e.g. 'P1Y2M').
  3. Validate interval strings in the application; use NULL for missing values.

Related & next steps

Reference: PostgreSQL 18 Section 8.5 “Date/Time Types”.

Was this helpful?