22007

At a glance

22007 is invalid_datetime_format. The fixed interval-formatting path reports an invalid format specification and explains that intervals are not tied to specific calendar dates.

The shared case formats interval '1 day' with the invalid ID mask, then uses DD as the repaired interval format. Send the two SELECT statements separately; the first is expected to fail before the repaired format runs. This verifies the interval-formatting path only; the runner owns session cleanup.

SELECT to_char(interval '1 day', 'ID');
SELECT to_char(interval '1 day', 'DD');

Calibration observed the interval DCH formatting path: an invalid format reported invalid format specification for an interval value with hint Intervals are not tied to specific calendar dates.; a valid DD format returned 01, and both runner autocommit sessions returned to IDLE. This run covers interval formatting only; general date parsing and DateStyle remain source/document scope.

Messages

The INVALID_FOR_INTERVAL guard raises ERROR with primary invalid format specification for an interval value and HINT Intervals are not tied to specific calendar dates. The cited branch has no separate DETAIL. In the fixed datetime input parser, the DTERR_BAD_FORMAT/default branch maps 22007 to the common primary template invalid input syntax for type %s: "%s"; the adjacent field-overflow and month/day-overflow branches use 22008 instead, with the latter adding HINT Perhaps you need a different "DateStyle" setting. Other datetime-format parsers can use 22007 with different primary/detail/hint variants, so retain the full diagnostic.

Meaning

22007 means the datetime input or format specification is invalid for the selected conversion. The fixed 18.6 interval-formatting path reports invalid format specification for an interval value and explains that intervals are not tied to specific calendar dates. In this DCH path, ID is a calendar-specific day-of-week token rejected for an interval, while DD is accepted. Fixed date input parsing routes text through ParseDateTime/DecodeDateTime and DateTimeParseError: the bad-format/default branch uses invalid input syntax for type %s: "%s"; field overflow belongs to 22008, whose month/day ambiguity branch can suggest a different DateStyle. The 18.6 documentation says DateStyle selects the interpretation order for ambiguous numeric input. DateTimeParseError can fill an ErrorSaveContext instead of throwing, so soft-validation callers differ from normal cast/input calls that propagate ERROR.

Diagnosis

Record the original text, target type, DateStyle, TimeZone, format mask, and whether the operation was a cast, input function, to_date/to_timestamp, or interval formatting. Test the same text under the deployed session settings before changing the data. Distinguish an invalid token or separator from a field such as month/day that parsed but is out of range; those paths may produce 22008 instead.

Response

Make the input unambiguous—prefer an explicit format or ISO form where appropriate—and set session parsing options deliberately at the application boundary. For interval formatting, use a mask supported for intervals rather than a calendar-date mask. The frozen case used autocommit, so the failed formatting call left the session IDLE; inside an explicit transaction, roll back the transaction or roll back to a pre-existing savepoint before continuing. Reject or repair invalid text before writing; do not silently reinterpret it under a different DateStyle.

Versions

The locked catalogue records this condition from 7.4 in the listed formal snapshots and 19beta3; fixed source coverage is PostgreSQL 18.6.

Related

22008 for datetime field/range overflow, 22009 for timezone displacement, and 22003 for numeric range failures.

Sources

The fixed interval-formatting path is formatting.c#L555-L558. Fixed date input dispatch is in date.c#L110-L178, and the common parser error mapping is in datetime.c#L4195-L4266. PostgreSQL 18’s date/time input documentation documents DateStyle and ambiguous date text. The structured evidence record pins the fixed sources, message, and hint.