feat: Add TRY_TO_TIMESTAMP for Snowflake back end support

[StephenAtKinetiq]

Is your feature request related to a problem?

Failure when invalid data in a column with just to_timestamp

What is the motivation behind your request?

Simplify date conversion from messy data

Describe the solution you'd like

try_to_timestamp()

What version of ibis are you running?

ibis-framework 8.0.0

What backend(s) are you using, if any?

Snowflake

Code of Conduct

• I agree to follow this project's Code of Conduct

[cpcloud]

Thanks for opening an issue!

Does col.try_cast("timestamp") work for you?

API docs for try_cast.

[StephenAtKinetiq]

Thanks, I am trying to make this a "try":
_.startdatetimeutc.to_timestamp("YYYY-MM-DD HH24:MI:SS.FF")

Which would require a 3rd positional, like this:
_.startdatetimeutc.try_cast("timestamp","YYYY-MM-DD HH24:MI:SS.FF")

which returns with Ibis 8 and Snowflake,

No translation rule for <class 'ibis.expr.operations.generic.TryCast'>

I am moving to ibis from dplyr/dbplyr in R, so I may be overlooking something.

[cpcloud]

Ah, so this is really a "try to parse and return NULL if the pattern doesn't match" function it seems.

For now you should be able to use our builtin function support for this:

@ibis.udf.scalar.builtin
def try_to_timestamp(input, fmt: str) -> dt.Timestamp:
    ...

You can then call this function on an Ibis expression:

t.mutate(ts=try_to_timestamp(_.startdatetimeutc, "YYYY-MM-DD HH24:MI:SS.FF"))

[StephenAtKinetiq]

This looks promising, but I am overlooking some piece of this puzzle. I modified the above as:

import ibis
import ibis.expr.datatypes as dt
ibis.options.interactive = True


@ibis.udf.scalar.builtin
def try_to_timestamp(input, fmt: str) -> dt.Timestamp:
    ...

and get the error:

TypeError: Cannot construct a parametric Timestamp datatype based on the type itself

Tried searching for a similar example, but I am missing something...

Thanks!

[cpcloud]

Hm, you might need to write dt.timestamp (lowercase t) 🤔

[cpcloud]

More generally, we could probably add a try_parse to Timestmap/Date/Time expressions if that's something supported by a sizable number of backends.

[StephenAtKinetiq]

I have now gotten past the earlier issue. But when I try to use the scalar function:

import ibis
import ibis.expr.datatypes as dt
ibis.options.interactive = True


@ibis.udf.scalar.builtin
def try_to_timestamp(input, fmt: str) -> dt.timestamp:
    ...

(
    T012_DF_OCCURRENCES_0
        .head()
        .mutate(DT_STOP_UTC = _.stopdatetimeUtc.try_to_timestamp("YYYY-MM-DD HH24:MI:SS.FF"),)
)

I get:

AttributeError: 'StringColumn' object has no attribute 'try_to_timestamp'

[cpcloud]

You have to invoke the UDF as a function (we don't magically add methods to things).

    T012_DF_OCCURRENCES_0
        .head()
        .mutate(DT_STOP_UTC = try_to_timestamp(_.stopdatetimeUtc, "YYYY-MM-DD HH24:MI:SS.FF"),)

[StephenAtKinetiq]

Thanks so much for indulging me, this worked. I sincerely appreciate your help and this great project!