22007 — invalid_datetime_format

日期时间格式串无效，如 interval 格式错误。

英文手册覆盖始于 PostgreSQL 8.1. 来源历史与运行验证各自保留独立版本边界。

当前阅读 PG 18·选择有来源记录的版本

8.1 8.2 8.3 8.4 9.0 9.1 9.2 9.3 9.4 9.5 9.6 10 11 12 13 14 15 16 17 18 19 20

aliases: 未知
class name: Data Exception
condition name: invalid_datetime_format
lang: en
sqlstate: 22007

版本定义 PG 18

22007

速览 {#at-a-glance}

固定 interval 格式化路径报告 invalid format specification for an interval value，并说明 interval 不绑定到特定日历日期。

共享案例用无效的 ID mask 格式化 interval '1 day'，再用 DD 作为修复后的 interval 格式。本案例应分开发送两条 SELECT；第一条预期失败，之后再执行修复格式。本案例只验证 interval 格式化路径；会话清理由运行器负责。

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

校准实测了 interval 的 DCH 格式化路径：无效格式报告 invalid format specification for an interval value，提示为 Intervals are not tied to specific calendar dates.；有效 DD 格式返回 01，运行器的两条自动提交会话均回到 IDLE。本次运行只覆盖 interval 格式化；一般日期解析和 DateStyle 仍属于源码/文档范围。

报文 {#messages}

INVALID_FOR_INTERVAL guard 以 ERROR 严重性报告 primary：invalid format specification for an interval value，HINT 为 Intervals are not tied to specific calendar dates.。引用分支没有独立 DETAIL。固定日期输入解析的 DTERR_BAD_FORMAT/default 分支把 22007 映射为通用 primary 模板 invalid input syntax for type %s: "%s"；相邻的字段越界和月日越界分支使用 22008，后者才会增加 HINT Perhaps you need a different "DateStyle" setting.。其他日期时间格式解析器也可能使用 22007 并产生不同的 primary/detail/hint，因此应保留完整诊断。

含义 {#meaning}

22007 表示所选日期时间转换的输入或格式说明无效。固定 18.6 interval 格式化路径报告 invalid format specification for an interval value，并说明 interval 不绑定到特定日历日期。在这个 DCH 路径中，ID 是带日历语义的星期几 token，interval 不支持；DD 则可用。固定日期输入路径会通过 ParseDateTime/DecodeDateTime 和 DateTimeParseError 处理文本：bad-format/default 分支使用 invalid input syntax for type %s: "%s"；字段越界属于 22008，其月日歧义分支才可能提示调整 DateStyle。18.6 文档说明 DateStyle 选择含糊数字日期的解释顺序。DateTimeParseError 可以填充 ErrorSaveContext 而不直接抛错，因此 soft-validation 调用与正常 cast/input 传播 ERROR 的行为不同。

诊断 {#diagnosis}

记录原始文本、目标类型、DateStyle、TimeZone、format mask，以及操作是 cast、输入函数、to_date/to_timestamp 还是 interval 格式化。在改变数据前，用部署会话设置重现同一文本。区分无效 token/分隔符与已解析但超出范围的月日字段；后者可能产生 22008。

处置 {#response}

让输入无歧义，在适当场景使用显式格式或 ISO 形式，并在应用边界明确设置会话解析选项。interval 格式化应使用支持 interval 的 mask，而不是日历日期 mask。本次固定案例使用自动提交，失败格式化调用后会话仍为 IDLE；显式事务中应先回滚整个事务，或回滚到失败调用前已有的保存点，再继续执行。写入前拒绝或修正无效文本，不要用切换 DateStyle 的方式静默改写含义。

版本 {#versions}

锁定目录从 7.4 记录该条件，并在列出的正式快照及 19beta3 中出现；固定源码覆盖为 PostgreSQL 18.6。

来源 {#sources}

固定 interval 格式化路径见 formatting.c#L555-L558；固定日期输入 dispatch 见 date.c#L110-L178，通用解析错误映射见 datetime.c#L4195-L4266。PostgreSQL 18 的日期时间输入文档说明了 DateStyle 和含糊日期文本。结构化证据记录固定了这些源码、消息与 hint。

比较版本

SQLSTATE 证据与已记录的运行版本

cases · invalid_interval_format

{
  "assertions": [
    "SQLSTATE is 22007",
    "The diagnostic and HINT identify the interval format restriction",
    "The failed autocommit session remains IDLE",
    "A valid interval format returns the expected value afterward"
  ],
  "case_id": "invalid_interval_format",
  "cleanup": "Drop the case schema with an owner connection.",
  "has_snippet": true,
  "position": 0,
  "preconditions": [],
  "repair": "Use an interval-compatible format such as DD, or convert a value whose semantics really require a calendar date before formatting.",
  "trigger": "Format an interval with the date-only field ID, which is not defined for intervals.",
  "versions": [
    "10",
    "18"
  ]
}

claims · identity

{
  "claim_id": "identity",
  "limits": "",
  "method": "Read fixed definition.",
  "position": 0,
  "runtime": [],
  "sources": [
    "src.errcodes.18.6"
  ],
  "statement": "22007 is invalid_datetime_format in SQLSTATE Class 22."
}

claims · mechanism.interval-format

{
  "claim_id": "mechanism.interval-format",
  "limits": "The selected runtime covers interval formatting only.",
  "method": "Read the INVALID_FOR_INTERVAL macro and both message fields.",
  "position": 1,
  "runtime": [],
  "sources": [
    "src.path.interval-format"
  ],
  "statement": "The interval/time formatting guard rejects calendar-specific format tokens for interval values and supplies a fixed hint explaining the calendar boundary."
}

claims · mechanism.datetime-input

{
  "claim_id": "mechanism.datetime-input",
  "limits": "This confirms source and documentation scope only; the selected runtime covers interval formatting, not DateStyle parsing.",
  "method": "Read the fixed date input/parser and error mapping, then the same-version documentation.",
  "position": 2,
  "runtime": [],
  "sources": [
    "src.path.date-input",
    "src.path.datetime-error",
    "doc.datetime-input.18.6"
  ],
  "statement": "Fixed date input dispatches text through ParseDateTime and DecodeDateTime. DateTimeParseError maps the DTERR_BAD_FORMAT/default branch to 22007 with the common primary invalid input syntax for type template; field overflow and month/day overflow use 22008, with the latter adding a DateStyle hint. The PostgreSQL 18 documentation describes DateStyle as selecting the interpretation order for ambiguous numeric date input, and the parser can fill ErrorSaveContext instead of throwing for soft-validation callers."
}

claims · history

{
  "claim_id": "history",
  "limits": "",
  "method": "Use catalogue boundary.",
  "position": 3,
  "runtime": [],
  "sources": [
    "src.errcodes.18.6"
  ],
  "statement": "The locked catalogue records 22007 from 7.4 without proving an exact implementation introduction date."
}

claims · runtime.observation

{
  "claim_id": "runtime.observation",
  "limits": "The observation does not generalize to DateStyle parsing or every date/time formatter.",
  "method": "Execute the shared registry and inspect selected summaries.",
  "position": 4,
  "runtime": [
    "runtime.22007-data1-latest.latest",
    "runtime.22007-data1-pg10.pg10"
  ],
  "sources": [
    "manifest.22007",
    "snippet-registry.22007"
  ],
  "statement": "The invalid_interval_format case passed on isolated PostgreSQL 18.6 and 10.21: the interval mask ID raised 22007 with the fixed hint, left the autocommit session IDLE, and DD returned 01."
}

messages · message.interval

{
  "limits": "",
  "message_id": "message.interval",
  "path": "",
  "position": 0,
  "raw": {
    "hint_template": "Intervals are not tied to specific calendar dates.",
    "id": "message.interval",
    "primary_template": "invalid format specification for an interval value",
    "severity_source": "ERROR",
    "sources": [
      "src.path.interval-format"
    ],
    "sqlstate": "22007"
  },
  "severity": "ERROR",
  "sources": [
    "src.path.interval-format"
  ],
  "templates": [
    {
      "kind": "primary",
      "literal": "invalid format specification for an interval value",
      "position": 0,
      "role": "",
      "template": "invalid format specification for an interval value"
    },
    {
      "kind": "hint",
      "literal": "Intervals are not tied to specific calendar dates.",
      "position": 1,
      "role": "",
      "template": "Intervals are not tied to specific calendar dates."
    }
  ]
}

messages · message.datetime-input

{
  "limits": "",
  "message_id": "message.datetime-input",
  "path": "",
  "position": 1,
  "raw": {
    "id": "message.datetime-input",
    "primary_template": "invalid input syntax for type %s: \"%s\"",
    "severity_source": "ERROR",
    "sources": [
      "src.path.datetime-error"
    ],
    "sqlstate": "22007"
  },
  "severity": "ERROR",
  "sources": [
    "src.path.datetime-error"
  ],
  "templates": [
    {
      "kind": "primary",
      "literal": "invalid input syntax for type : \" \"",
      "position": 0,
      "role": "",
      "template": "invalid input syntax for type %s: \"%s\""
    }
  ]
}

runtimes · runtime.22007-data1-latest.latest · 18.6 (Homebrew) · passed

{
  "cases": [
    "invalid_interval_format"
  ],
  "limits": "",
  "position": 0,
  "raw": {
    "cases": [
      "invalid_interval_format"
    ],
    "id": "runtime.22007-data1-latest.latest",
    "observed": {
      "sqlstate": "22007",
      "status_after_repair": "IDLE"
    },
    "raw": "verify/results/22007-data1-latest/latest/raw.jsonl",
    "raw_sha256": "e1ab1c24625152560f9dc7baf091dbec22324d34bba0deea9c8048565500906a",
    "run_id": "22007-data1-latest",
    "server_version": "18.6 (Homebrew)",
    "server_version_num": 180006,
    "snippet_registry": {
      "path": "verify/cases/22007/snippets.json",
      "sha256": "cea6f9f6a07e3371f1ebc79d2842da2c66c06750f3b39f91b7fdd57fb2b7d0a9"
    },
    "status": "passed",
    "summary": "verify/results/22007-data1-latest/latest/summary.json",
    "summary_sha256": "5429879e671be538f513654b5c0821f227aac67f67e0b1fbb2d6c7821c0af675",
    "target": "latest"
  },
  "run_id": "22007-data1-latest",
  "runtime_id": "runtime.22007-data1-latest.latest",
  "server_version": "18.6 (Homebrew)",
  "status": "passed",
  "target": "latest"
}

runtimes · runtime.22007-data1-pg10.pg10 · 10.21 (Debian 10.21-1.pgdg90+1) · passed

{
  "cases": [
    "invalid_interval_format"
  ],
  "limits": "",
  "position": 1,
  "raw": {
    "cases": [
      "invalid_interval_format"
    ],
    "id": "runtime.22007-data1-pg10.pg10",
    "observed": {
      "sqlstate": "22007",
      "status_after_repair": "IDLE"
    },
    "raw": "verify/results/22007-data1-pg10/pg10/raw.jsonl",
    "raw_sha256": "e9f8cef16bd8674c68fb7c518fa325adbf726e8797dc8b43da20508a0ecc41dd",
    "run_id": "22007-data1-pg10",
    "server_version": "10.21 (Debian 10.21-1.pgdg90+1)",
    "server_version_num": 100021,
    "snippet_registry": {
      "path": "verify/cases/22007/snippets.json",
      "sha256": "cea6f9f6a07e3371f1ebc79d2842da2c66c06750f3b39f91b7fdd57fb2b7d0a9"
    },
    "status": "passed",
    "summary": "verify/results/22007-data1-pg10/pg10/summary.json",
    "summary_sha256": "dbc6026274c92764eb19acdc4668d1aa3bb2529a01a7ca3dbcb879cb46646f9c",
    "target": "pg10"
  },
  "run_id": "22007-data1-pg10",
  "runtime_id": "runtime.22007-data1-pg10.pg10",
  "server_version": "10.21 (Debian 10.21-1.pgdg90+1)",
  "status": "passed",
  "target": "pg10"
}

sources · src.errcodes.18.6

{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/utils/errcodes.txt",
  "position": 0,
  "sha256": "6e8de346643ba84aa3c9c6a73360acfc7b2dfb89162c06c08ce9bf5bcd5bbcba",
  "source_id": "src.errcodes.18.6",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/errcodes.txt#L179"
}

sources · src.calls.REL_18_6

{
  "commit": "",
  "docs_url": "",
  "kind": "local_artifact",
  "location": "",
  "path": "raw/calls/REL_18_6.jsonl",
  "position": 1,
  "sha256": "9ee8a0e81d8f0825c5c1ae45583439859a26e602bdd4ce2f2a62aa278867ccbf",
  "source_id": "src.calls.REL_18_6",
  "tag": "",
  "url": ""
}

sources · src.path.interval-format

{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/utils/adt/formatting.c",
  "position": 2,
  "sha256": "a22651cc223e93c1879420004027325579fc79a3ba8684408496149164a2220c",
  "source_id": "src.path.interval-format",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/adt/formatting.c#L548-L559"
}

sources · src.path.date-input

{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/utils/adt/date.c",
  "position": 3,
  "sha256": "4834c868f3461822eb0de972fe35b04f9fb9ba75cf2fa9794a67259536f35773",
  "source_id": "src.path.date-input",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/adt/date.c#L110-L178"
}

sources · src.path.datetime-error

{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/utils/adt/datetime.c",
  "position": 4,
  "sha256": "709e05b536f611f7eeaaabb716501d59d4d1e87c53757e15240fe1fc4845459c",
  "source_id": "src.path.datetime-error",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/adt/datetime.c#L4195-L4266"
}

sources · doc.datetime-input.18.6

{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "https://www.postgresql.org/docs/18/datatype-datetime.html#DATATYPE-DATETIME-INPUT",
  "kind": "upstream_source",
  "location": "",
  "path": "doc/src/sgml/datatype.sgml",
  "position": 5,
  "sha256": "86328daa77e20d81d222376ec0306d9841a17104e436c08d138eb763aefb3700",
  "source_id": "doc.datetime-input.18.6",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/doc/src/sgml/datatype.sgml#L1878-L1896"
}

sources · snippet-registry.22007

{
  "commit": "",
  "docs_url": "",
  "kind": "local_artifact",
  "location": "",
  "path": "verify/cases/22007/snippets.json",
  "position": 6,
  "sha256": "cea6f9f6a07e3371f1ebc79d2842da2c66c06750f3b39f91b7fdd57fb2b7d0a9",
  "source_id": "snippet-registry.22007",
  "tag": "",
  "url": ""
}

sources · manifest.22007

{
  "commit": "",
  "docs_url": "",
  "kind": "local_artifact",
  "location": "",
  "path": "verify/cases/22007/cases.json",
  "position": 7,
  "sha256": "bc62fd20d13379af121cdbd76080fea124206647dfcac6c27737ceabf21b2af4",
  "source_id": "manifest.22007",
  "tag": "workspace",
  "url": "verify/cases/22007/cases.json"
}

来源引用

原始手册引用

完整定义与证据 JSON

定义来源

center · PostgreSQL 18 · english-manuals:0174675f9bb187bc6f0337a1d9318c28119611bbc8a8064a3e157afb4a243f2b

正文语言: zh-Hans · english-manuals:0174675f9bb187bc6f0337a1d9318c28119611bbc8a8064a3e157afb4a243f2b