prestodb/presto · error · PrestoException

INVALID_CAST_ARGUMENT

INVALID_CAST_ARGUMENT

Error message

Value cannot be cast to timestamp: 

What it means

Thrown by the VARCHAR->TIMESTAMP cast when legacy timestamp semantics are enabled: the string (after trimming) cannot be parsed by parseTimestampWithoutTimeZone with the session time zone, so IllegalArgumentException is wrapped as INVALID_CAST_ARGUMENT. The literal must be a valid timestamp without (or in legacy mode, with) a time zone.

Source

Thrown at presto-main-base/src/main/java/com/facebook/presto/type/TimestampOperators.java:212

            return utf8Slice(printTimestampWithoutTimeZone(properties.getTimeZoneKey(), value));
        }
        else {
            return utf8Slice(printTimestampWithoutTimeZone(value));
        }
    }

    @ScalarOperator(CAST)
    @LiteralParameters("x")
    @SqlType(StandardTypes.TIMESTAMP)
    public static long castFromSlice(SqlFunctionProperties properties, @SqlType("varchar(x)") Slice value)
    {
        // This accepts value with or without time zone
        if (properties.isLegacyTimestamp()) {
            try {
                return parseTimestampWithoutTimeZone(properties.getTimeZoneKey(), trim(value).toStringUtf8());
            }
            catch (IllegalArgumentException e) {
                throw new PrestoException(INVALID_CAST_ARGUMENT, "Value cannot be cast to timestamp: " + value.toStringUtf8(), e);
            }
        }
        else {
            try {
                return parseTimestampWithoutTimeZone(trim(value).toStringUtf8());
            }
            catch (IllegalArgumentException e) {
                throw new PrestoException(INVALID_CAST_ARGUMENT, "Value cannot be cast to timestamp: " + value.toStringUtf8(), e);
            }
        }
    }

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

View on GitHub (pinned to 55bb57d202)

Solutions

1. Reformat the string to ISO 8601 'YYYY-MM-DD HH:mm:ss[.SSS]' before casting.
2. Use try_cast(col AS TIMESTAMP) to skip/NULL bad rows.
3. If strings carry offsets, cast to TIMESTAMP WITH TIME ZONE or strip the offset.
4. Check the session/catalog legacy timestamp property and align parsing accordingly (consider migrating off legacy_timestamp).

Example fix

// before
SELECT CAST(ts_text AS TIMESTAMP) FROM logs; -- '01/02/2024 10:00'
// after
SELECT CAST(date_parse(ts_text, '%m/%d/%Y %H:%i') AS TIMESTAMP) FROM logs;

-- Guard: ISO-like timestamp shape check before casting
SELECT * FROM t WHERE regexp_like(ts_str,
  '^\d{4}-\d{2}-\d{2}[ T]\d{2}:\d{2}:\d{2}(\.\d{1,9})?([+-]\d{2}:?\d{2}|Z)?$');

SELECT CASE WHEN regexp_like(ts_str, '^\d{4}-\d{2}-\d{2}[ T]\d{2}:\d{2}:\d{2}.*$')
       THEN CAST(ts_str AS TIMESTAMP) END AS safe_ts FROM t;

// JDBC
try { rs = stmt.executeQuery(tsCastSql); }
catch (SQLException e) {
  if (e.getMessage() != null && e.getMessage().contains("Value cannot be cast to timestamp")) {
    // inspect offending literal; rerun with try_cast or date_parse
  } else throw e;
}

When it happens

Trigger: CAST(varchar_col AS TIMESTAMP) under legacy_timestamp=true where the string is malformed ('2024-13-01', 'now', '2024/01/02 10:00'), or includes timezone info the legacy parser rejects.

Common situations: Clusters still on legacy timestamp behavior parsing mixed-format log dates; ingestion pipelines feeding 'MM/DD/YYYY' style strings; strings with 'Z' or offsets under legacy parsing.

Related errors

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

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