BigQuery : Returning timestamp from JS udf throwing "Failed to coerce output value to type TIMESTAMP"

Question

I have a bigquery code.

CREATE TEMP FUNCTION to_struct_attributes(input STRING)
RETURNS STRUCT<status_code STRING, created_time TIMESTAMP>
LANGUAGE js AS """
    let res = JSON.parse(input);
    res['created_time'] = Date(res['created_time'])
    return res;
""";

SELECT 
5 AS ID,
to_struct_attributes(
    TO_JSON_STRING(
        STRUCT(
           TIMESTAMP(PARSE_TIMESTAMP('%Y%m%d%H%M%S', '20220215175959','America/Los_Angeles')) AS created_time 
        )
    )
) AS ATTRIBUTES;

When I execute this, I'm getting the following error:

Failed to coerce output value "2022-02-16 01:59:59+00" to type TIMESTAMP

I feel this is quite strange, since BigQuery should be able to interpret it correctly and I haven't had this issue with any other datatypes. Also, if I do:

SELECT TIMESTAMP("2022-02-16 01:59:59+00")

It returns:

2022-02-16 01:59:59 UTC

So BigQuery can indeed parse it correctly. I'm not sure why it doesn't happen for the UDF. On searching the internet, I found this question and as the answer suggests, if I change the return statement to:

return Date(res.created_time);

It resolves the issue. But for a project of mine, doing it for every timestamp is not feasible due to the high number of struct columns.

So, I wanted to know if someone has a better alternative to it?

PS : I have removed a lot of non-essential parts from the above example, so this might look a bit abstract. Also, the actual use-case is a bit different and complex that's why I need that JS udf.

Answer 1

The best way to do what you want is to implement the following code.

return Date(res.created_time);

This happens when you pass a TIMESTAMP to a UDF, it is represented as a DATE object, as stated in the documentation. This is like a return of a TIMESTAMP from a JavaScript UDF, where you need to construct and return a DATE object.