PostgreSQL 22031 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 22031: Invalid Argument for SQL JSON Datetime Function

PostgreSQL error code 22031 (invalid argument for sql json datetime function) occurs when the .datetime() method inside a SQL/JSON path expression receives a value it cannot parse as a valid date or timestamp. This typically happens when JSON string values don't conform to ISO 8601 format, or when a provided format template doesn't match the actual data. It's most commonly seen when using jsonb_path_query(), jsonb_path_exists(), or jsonb_path_match() functions.

Top 3 Causes

1. Non-ISO 8601 Date Strings in JSON

PostgreSQL's .datetime() method strictly expects ISO 8601 formatted strings by default. Passing regional or legacy date formats without a format template will trigger this error.

-- This FAILS: non-standard date format
SELECT jsonb_path_query(
    '{"date": "01/15/2024"}',
    '$.date.datetime()'
);
-- ERROR:  22031: invalid argument for sql json datetime function

-- This WORKS: provide a matching format template
SELECT jsonb_path_query(
    '{"date": "01/15/2024"}',
    '$.date.datetime("MM/DD/YYYY")'
);

-- This WORKS: ISO 8601 format needs no template
SELECT jsonb_path_query(
    '{"date": "2024-01-15"}',
    '$.date.datetime()'
);

2. Mismatched Format Template

When supplying a custom format pattern to .datetime(), the pattern must match the actual string exactly — including separators and field order.

-- This FAILS: format template doesn't match the actual string
SELECT jsonb_path_query(
    '{"ts": "2024-01-15 10:30:00"}',
    '$.ts.datetime("YYYY/MM/DD")'
);
-- ERROR: 22031: invalid argument for sql json datetime function

-- This WORKS: template matches the data exactly
SELECT jsonb_path_query(
    '{"ts": "2024-01-15 10:30:00"}',
    '$.ts.datetime("YYYY-MM-DD HH24:MI:SS")'
);

-- Practical range filter example
SELECT id, payload
FROM orders
WHERE jsonb_path_exists(
    payload,
    '$.created_at.datetime("YYYY-MM-DD HH24:MI:SS") > $start',
    '{"start": "2024-01-01T00:00:00"}'::jsonb
);

3. Invalid or Missing Timezone Offset

When working with timezone-aware timestamps, an invalid timezone offset (e.g., +99:00) or a malformed timezone suffix in the JSON value causes error 22031. PostgreSQL applies strict validation on timezone offsets within SQL/JSON path processing.

-- This FAILS: invalid timezone offset
SELECT jsonb_path_query(
    '{"ts": "2024-01-15T10:30:00+99:00"}',
    '$.ts.datetime()'
);
-- ERROR: 22031: invalid argument for sql json datetime function

-- This WORKS: valid timezone offset
SELECT jsonb_path_query(
    '{"ts": "2024-01-15T10:30:00+09:00"}',
    '$.ts.datetime()'
);

-- Detect invalid timezone values before processing
SELECT id, payload->>'ts' AS bad_ts
FROM logs
WHERE NOT (
    payload->>'ts' ~ 
    '^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}([+-](0[0-9]|1[0-4]):[0-5]\d|Z)?$'
);

Quick Fix Solutions

-- Option 1: Cast at SQL level instead of using .datetime()
SELECT *
FROM events
WHERE (payload->>'event_date')::date > '2024-01-01';

-- Option 2: Normalize stored JSON dates with an UPDATE
UPDATE events
SET payload = jsonb_set(
    payload,
    '{event_date}',
    to_jsonb(to_date(payload->>'event_date', 'MM/DD/YYYY')::text)
)
WHERE payload->>'event_date' ~ '^\d{2}/\d{2}/\d{4}$';

-- Option 3: Safe wrapper function to avoid runtime errors
CREATE OR REPLACE FUNCTION safe_json_to_timestamp(val text)
RETURNS timestamptz AS $$
BEGIN
    RETURN val::timestamptz;
EXCEPTION WHEN OTHERS THEN
    RETURN NULL;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

SELECT safe_json_to_timestamp(payload->>'ts')
FROM logs;

Prevention Tips

Enforce ISO 8601 on insert. Use a trigger or application-level validation to ensure all date/time values stored in JSONB columns follow ISO 8601 format (YYYY-MM-DD or YYYY-MM-DDTHH:MI:SS±HH:MM). This eliminates the need for format templates and makes .datetime() calls reliable.

Validate before querying. When consuming data from external systems or legacy pipelines, run a pre-check query using regex to identify non-conforming records before running JSON path queries with .datetime(). This is especially important in ETL jobs and batch processing workflows where bad records can silently break downstream logic.

Related Errors

Code	Name	Notes
`22007`	`invalid_datetime_format`	Similar but occurs in standard SQL casting
`22P02`	`invalid_text_representation`	Text-to-type conversion failure
`42883`	`undefined_function`	Occurs on PostgreSQL < 12 where `.datetime()` isn't supported

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community