prestodb/presto · error · PrestoException

INVALID_CAST_ARGUMENT

INVALID_CAST_ARGUMENT

Error message

Value cannot be cast to timestamp with time zone: 

What it means

This error is thrown when a VARCHAR value cannot be parsed into a TIMESTAMP WITH TIME ZONE. castFromSlice trims the input string and calls parseTimestampWithTimeZone; if the parser raises IllegalArgumentException (bad format, unparseable date/time, unknown zone), Presto wraps it in INVALID_CAST_ARGUMENT with the offending value in the message. It backs the SQL CAST(varchar AS timestamp with time zone) operator.

Source

Thrown at presto-main-base/src/main/java/com/facebook/presto/type/TimestampWithTimeZoneOperators.java:200

    @ScalarOperator(CAST)
    @LiteralParameters("x")
    @SqlType("varchar(x)")
    public static Slice castToSlice(@SqlType(StandardTypes.TIMESTAMP_WITH_TIME_ZONE) long value)
    {
        return utf8Slice(printTimestampWithTimeZone(value));
    }

    @ScalarOperator(CAST)
    @LiteralParameters("x")
    @SqlType(StandardTypes.TIMESTAMP_WITH_TIME_ZONE)
    public static long castFromSlice(SqlFunctionProperties properties, @SqlType("varchar(x)") Slice value)
    {
        try {
            return parseTimestampWithTimeZone(properties.getTimeZoneKey(), trim(value).toStringUtf8());
        }
        catch (IllegalArgumentException e) {
            throw new PrestoException(INVALID_CAST_ARGUMENT, "Value cannot be cast to timestamp with time zone: " + value.toStringUtf8(), e);
        }
    }

    @ScalarOperator(HASH_CODE)
    @SqlType(StandardTypes.BIGINT)
    public static long hashCode(@SqlType(StandardTypes.TIMESTAMP_WITH_TIME_ZONE) long value)
    {
        return AbstractLongType.hash(unpackMillisUtc(value));
    }

    @ScalarOperator(XX_HASH_64)
    @SqlType(StandardTypes.BIGINT)
    public static long xxHash64(@SqlType(StandardTypes.TIMESTAMP_WITH_TIME_ZONE) long value)
    {
        return XxHash64.hash(unpackMillisUtc(value));
    }

    @ScalarOperator(IS_DISTINCT_FROM)

View on GitHub (pinned to 55bb57d202)

Solutions

1. Inspect the failing value in the message and correct the source data or expression
2. Normalize the string first with date_format/date_parse or a regex to the expected 'YYYY-MM-DD HH:MM:SS ±zone' format
3. Use try_cast instead of cast to get NULL instead of a query failure for bad rows
4. Filter or route unparseable rows out with a WHERE ... IS NOT NULL after try_cast

Example fix

// before
SELECT CAST(ts_str AS timestamp with time zone) FROM events;
// after
SELECT try_cast(ts_str AS timestamp with time zone) AS ts, ts_str FROM events WHERE try_cast(ts_str AS timestamp with time zone) IS NULL; -- inspect bad rows

// Presto SQL: pre-check before CAST
SELECT * FROM t
WHERE ts_str IS NOT NULL
  AND regexp_like(trim(ts_str), '^\\d{4}-\\d{2}-\\d{2} [\\d:]+( [+-]\\d{2}:?\\d{2}| .+)?$');

// Use try_cast to avoid hard failure
SELECT try_cast(ts_str AS timestamp with time zone) AS ts FROM t; -- NULL for bad rows

When it happens

Trigger: CAST('not-a-date' AS timestamp with time zone); a varchar column holding malformed timestamps passed through the cast; strings using a date/time format the parser does not accept (e.g. wrong separator, missing time zone offset); empty or whitespace-only strings.

Common situations: ETL pipelines ingesting text files/CSVs with inconsistent timestamp formats; locale-specific date strings ('12/31/2021') that the ISO-style parser rejects; time zone names misspelled or not in the IANA database; upstream schema changes silently altering column formats.

Related errors

• INVALID_CAST_ARGUMENT
• TimestampWithTimeZone overflow: %s ms
• nanos must be in range [0, 999_999_999]:
• Type must be a TimestampType for TimeStampSecVector
• ELASTICSEARCH_TYPE_MISMATCH

AI-assisted analysis of prestodb/presto@55bb57d202 (2026-09-04). Data as JSON: /api/errors/d81410710979d846. Report an issue: GitHub.