CODEWITHSUNDEEP
google-bigquery
Ryan
Ryan

Reputation: 45

BigQuery UDF Array<TIMESTAMP> return. Failed to coerce output value to type TIMESTAMP

I am using a BigQuery JavaScript UDF to return an array of dates. The array must a timestamp array, similar to the GENERATE_TIMESTAMP_ARRAY() function in order to match an UNNEST CASE WHEN.

The problem occurs when I return the timestamp array from my JavaScript UDF. The array returns fine if I specify RETURNS ARRAY<STRING>, but when I use RETURNS ARRAY<TIMESTAMP> BigQuery seems to not be able to coerce the string format of the dates.

I have tried numerous string formats, i.e. 'YYYY-MM-DD HH:mm:ss', 'YYYY-MM-DD 00:00:00+00', 'YYYY-MM-DD HH:mm:ss.SSSSSS UTC'.

CREATE TEMP FUNCTION
  getCalendarDateArray(date_start STRING,
    date_end STRING,
    frequency STRING,
    unit STRING,
    value INT64,
    parent STRING,
    next_date TIMESTAMP)

  RETURNS ARRAY<TIMESTAMP>

  LANGUAGE js AS

"""

    let dates_array = getTimestampArray(date_start, date_end, frequency, unit, value, parent, next_date);
    return dates_array;


"""
OPTIONS
  ( library=["gs://my-bucket/myfunction.js"] );

UDF output is currently an array of formatted date strings which cannot be coerced to timestamp. What is the proper string format in order for BigQuery to coerce to timestamp successfully?

[
      "2019-07-02",
      "2019-07-09",
      "2019-07-16",
      "2019-07-23",
      "2019-07-30"
    ]

Upvotes: 2

Views: 2778

Answers (3)

kalona
kalona

Reputation: 21

I am trying this with non-TEMP UDF functions but I get the compilation timestamp constantly. Is this the intended behavior?

P.S. No JavaScript expert :)

CREATE OR REPLACE FUNCTION my_dataset.my_ts() RETURNS TIMESTAMP LANGUAGE js AS """return Date(Date.now())""";

Upvotes: 0

Vadim Savenkov
Vadim Savenkov

Reputation: 1

Just to extend the accepted answer by Felipe Hoffa, the following example probably shows what happens: 

CREATE TEMP FUNCTION getDate_fail() RETURNS TIMESTAMP 
LANGUAGE js AS """ return Date.now()""";

CREATE TEMP FUNCTION getDate_succeed() RETURNS TIMESTAMP 
LANGUAGE js AS """ return Date(Date.now())""";

The former function throws a coercion exception, while the latter succeeds.

Upvotes: 0

Felipe Hoffa
Felipe Hoffa

Reputation: 59235

Use Date():

CREATE TEMP FUNCTION
  getCalendarDateArray()
  RETURNS ARRAY<TIMESTAMP>
  LANGUAGE js AS
"""
  return [Date('2018-03-03 10:10:10')]
""";

SELECT getCalendarDateArray() dates

enter image description here

Upvotes: 3

Related Questions