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

Question

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"
    ]

Answer 1

Use Date():

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

SELECT getCalendarDateArray() dates