feat(trino): string→timestamp cast should read ISO 8601 with a T separator

[Ramin314]

Is your feature request related to a problem?

string_value.cast("timestamp") compiles to CAST(x AS TIMESTAMP) on every backend. On Trino / Athena that cast reads only the space-separated form 2018-05-02 15:00:00; the ISO 8601 form 2018-05-02T15:00:00, which is what most upstream systems emit, fails at run time with INVALID_CAST_ARGUMENT: Value cannot be cast to timestamp: 2018-05-02T15:00:00. DuckDB, Postgres, BigQuery and others read both forms, so the same ibis expression is portable everywhere except Trino.

import ibis
t = ibis.table({"s": "string"}, name="t")
print(ibis.to_sql(t.select(ts=t.s.cast("timestamp")), dialect="trino"))
# SELECT CAST("t0"."s" AS TIMESTAMP) AS "ts" FROM "t" AS "t0"

What is the motivation behind your request?

Trino has from_iso8601_timestamp, which reads every ISO 8601 form (date, date-time, offsets). Spelling the string→timestamp cast on Trino as CAST(from_iso8601_timestamp(x) AS TIMESTAMP) (or normalising the separator) would make .cast("timestamp") behave like it does on the other SQL backends. TryCast would wrap it in TRY(...).

Describe the solution you'd like

TrinoCompiler.visit_Cast / visit_TryCast: when from_.is_string() and to.is_timestamp(), emit from_iso8601_timestamp rather than a bare cast.

What version of ibis are you using?

12.0.0

[jagadeepmamidi]

Hi, I’d like to work on this issue. I’ll update the Trino string-to-timestamp cast handling and add regression tests covering ISO 8601 values with a T separator, while ensuring the currently supported timestamp formats continue to work. Could you please assign this to me?