Array functions

GoogleSQL for Bigtable supports the following array functions.

Function list

NameSummary
ARRAY_AGG Gets an array of values.
For more information, seeAggregate functions.
ARRAY_CONCAT Concatenates one or more arrays with the same element type into a single array.
ARRAY_CONCAT_AGG Concatenates arrays and returns a single array as a result.
For more information, seeAggregate functions.
ARRAY_FILTER Takes an array, filters out unwanted elements, and returns the results in a new array.
ARRAY_FIRST Gets the first element in an array.
ARRAY_INCLUDES Checks if there is an element in the array that is equal to a search value.
ARRAY_INCLUDES_ALL Checks if all search values are in an array.
ARRAY_INCLUDES_ANY Checks if any search values are in an array.
ARRAY_IS_DISTINCT Checks if an array contains no repeated elements.
ARRAY_LAST Gets the last element in an array.
ARRAY_LAST_N Gets the suffix of an array, consisting of the lastn elements.
ARRAY_LENGTH Gets the number of elements in an array.
ARRAY_OFFSET Searches an array from the beginning or ending and produces the zero-based offset for the first matching element.
ARRAY_OFFSETS Searches an array and gets the zero-based offsets for matching elements.
ARRAY_REVERSE Reverses the order of elements in an array.
ARRAY_SLICE Produces an array containing zero or more consecutive elements from an input array.
ARRAY_TO_STRING Produces a concatenation of the elements in an array as aSTRING value.
ARRAY_TRANSFORM Transforms the elements of an array, and returns the results in a new array.
GENERATE_ARRAY Generates an array of values in a range.
GENERATE_DATE_ARRAY Generates an array of dates in a range.
GENERATE_TIMESTAMP_ARRAY Generates an array of timestamps in a range.
JSON_QUERY_ARRAY Extracts a JSON array and converts it to a SQLARRAY<JSON-formatted STRING> value.
For more information, seeJSON functions.

ARRAY_CONCAT

ARRAY_CONCAT(array_expression[,...])

Description

Concatenates one or more arrays with the same element type into a single array.

The function returnsNULL if any input argument isNULL.

Note: You can also use the|| concatenation operatorto concatenate arrays.

Return type

ARRAY

Examples

SELECTARRAY_CONCAT([1,2],[3,4],[5,6])ascount_to_six;/*--------------------------------------------------+ | count_to_six                                     | +--------------------------------------------------+ | [1, 2, 3, 4, 5, 6]                               | +--------------------------------------------------*/

ARRAY_FILTER

ARRAY_FILTER(array_expression,lambda_expression)lambda_expression:{element_alias->boolean_expression|(element_alias,index_alias)->boolean_expression}

Description

Takes an array, filters out unwanted elements, and returns the results in a newarray.

  • array_expression: The array to filter.
  • lambda_expression: Each element inarray_expression is evaluated againstthelambda expression. If the expression evaluates toFALSE orNULL, the element is removed from the resulting array.
  • element_alias: An alias that represents an array element.
  • index_alias: An alias that represents the zero-based offset of the arrayelement.
  • boolean_expression: The predicate used to filter the array elements.

ReturnsNULL if thearray_expression isNULL.

Return type

ARRAY

Example

SELECTARRAY_FILTER([1,2,3],e->e >1)ASa1,ARRAY_FILTER([0,2,3],(e,i)->e >i)ASa2;/*-------+-------+ | a1    | a2    | +-------+-------+ | [2,3] | [2,3] | +-------+-------*/

ARRAY_FIRST

ARRAY_FIRST(array_expression)

Description

Takes an array and returns the first element in the array.

Produces an error if the array is empty.

ReturnsNULL ifarray_expression isNULL.

Note: To get the last element in an array, seeARRAY_LAST.

Return type

Matches the data type of elements inarray_expression.

Example

SELECTARRAY_FIRST(['a','b','c','d'])asfirst_element/*---------------+ | first_element | +---------------+ | a             | +---------------*/

ARRAY_INCLUDES

ARRAY_INCLUDES(array_to_search,search_value)

Description

Takes an array and returnsTRUE if there is an element in the array that isequal to the search_value.

  • array_to_search: The array to search.
  • search_value: The element to search for in the array.

ReturnsNULL ifarray_to_search orsearch_value isNULL.

Return type

BOOL

Example

In the following example, the query first checks to see if0 exists in anarray. Then the query checks to see if1 exists in an array.

SELECTARRAY_INCLUDES([1,2,3],0)ASa1,ARRAY_INCLUDES([1,2,3],1)ASa2;/*-------+------+ | a1    | a2   | +-------+------+ | false | true | +-------+------*/

ARRAY_INCLUDES_ALL

ARRAY_INCLUDES_ALL(array_to_search,search_values)

Description

Takes an array to search and an array of search values. ReturnsTRUE if allsearch values are in the array to search, otherwise returnsFALSE.

  • array_to_search: The array to search.
  • search_values: The array that contains the elements to search for.

ReturnsNULL ifarray_to_search orsearch_values isNULL.

Return type

BOOL

Example

In the following example, the query first checks to see if3,4, and5exists in an array. Then the query checks to see if4,5, and6 exists inan array.

SELECTARRAY_INCLUDES_ALL([1,2,3,4,5],[3,4,5])ASa1,ARRAY_INCLUDES_ALL([1,2,3,4,5],[4,5,6])ASa2;/*------+-------+ | a1   | a2    | +------+-------+ | true | false | +------+-------*/

ARRAY_INCLUDES_ANY

ARRAY_INCLUDES_ANY(array_to_search,search_values)

Description

Takes an array to search and an array of search values. ReturnsTRUE if anysearch values are in the array to search, otherwise returnsFALSE.

  • array_to_search: The array to search.
  • search_values: The array that contains the elements to search for.

ReturnsNULL ifarray_to_search orsearch_values isNULL.

Return type

BOOL

Example

In the following example, the query first checks to see if3,4, or5exists in an array. Then the query checks to see if4,5, or6 exists inan array.

SELECTARRAY_INCLUDES_ANY([1,2,3],[3,4,5])ASa1,ARRAY_INCLUDES_ANY([1,2,3],[4,5,6])ASa2;/*------+-------+ | a1   | a2    | +------+-------+ | true | false | +------+-------*/

ARRAY_IS_DISTINCT

ARRAY_IS_DISTINCT(value)

Description

ReturnsTRUE if the array contains no repeated elements, using the sameequality comparison logic asSELECT DISTINCT.

Return type

BOOL

Examples

SELECTARRAY_IS_DISTINCT([1,2,3])ASis_distinct/*-------------+ | is_distinct | +-------------+ | true        | +-------------*/
SELECTARRAY_IS_DISTINCT([1,1,1])ASis_distinct/*-------------+ | is_distinct | +-------------+ | false       | +-------------*/
SELECTARRAY_IS_DISTINCT([1,2,NULL])ASis_distinct/*-------------+ | is_distinct | +-------------+ | true        | +-------------*/
SELECTARRAY_IS_DISTINCT([1,1,NULL])ASis_distinct/*-------------+ | is_distinct | +-------------+ | false       | +-------------*/
SELECTARRAY_IS_DISTINCT([1,NULL,NULL])ASis_distinct/*-------------+ | is_distinct | +-------------+ | false       | +-------------*/
SELECTARRAY_IS_DISTINCT([])ASis_distinct/*-------------+ | is_distinct | +-------------+ | true        | +-------------*/
SELECTARRAY_IS_DISTINCT(NULL)ASis_distinct/*-------------+ | is_distinct | +-------------+ | NULL        | +-------------*/

ARRAY_LAST

ARRAY_LAST(array_expression)

Description

Takes an array and returns the last element in the array.

Produces an error if the array is empty.

ReturnsNULL ifarray_expression isNULL.

Note: To get the first element in an array, seeARRAY_FIRST.

Return type

Matches the data type of elements inarray_expression.

Example

SELECTARRAY_LAST(['a','b','c','d'])aslast_element/*---------------+ | last_element  | +---------------+ | d             | +---------------*/

ARRAY_LAST_N

ARRAY_LAST_N(input_array,n)

Description

Returns a suffix ofinput_array consisting of the lastn elements.

Caveats:

  • Ifinput_array isNULL, returnsNULL.
  • Ifn isNULL, returnsNULL.
  • Ifn is0, returns an empty array.
  • Ifn is longer thaninput_array, returnsinput_array.
  • Ifn is negative, produces an error.
Note: To return a prefix in a similar way, see[ARRAY_FIRST_N][array-first-n].

Return type

ARRAY

Example

SELECTARRAY_LAST_N([1,2,3,4,5],0)ASa,ARRAY_LAST_N([1,2,3,4,5],3)ASb,ARRAY_LAST_N([1,2,3,4,5],7)ASc/*----------------------------------+ | a  | b         | c               | +----------------------------------+ | [] | [3, 4, 5] | [1, 2, 3, 4, 5] | +----------------------------------*/
-- Error: out of boundsSELECTARRAY_LAST_N([1,2,3,4,5],-1)

ARRAY_LENGTH

ARRAY_LENGTH(array_expression)

Description

Returns the size of the array. Returns 0 for an empty array. ReturnsNULL ifthearray_expression isNULL.

Return type

INT64

Examples

SELECTARRAY_LENGTH(["coffee",NULL,"milk"])ASsize_a,ARRAY_LENGTH(["cake","pie"])ASsize_b;/*--------+--------+ | size_a | size_b | +--------+--------+ | 3      | 2      | +--------+--------*/

ARRAY_OFFSET

ARRAY_OFFSET(input_array,element_to_find[,first_or_last])element_to_find:{element_expression|element_lambda_expression}element_lambda_expression:element_alias->boolean_expression

Description

Searches an array from the beginning or ending and gets the zero-based offsetfor the first matching element. If no element is found, returnsNULL.

Arguments:

  • input_array: The array to search.
  • element_expression: The element to find in the array.Must be a comparable data type.
  • element_lambda_expression: Each element ininput_array is evaluatedagainst the lambda expression. If the expression evaluates toTRUE, theelement is included in the search results.
  • element_alias: An alias that represents the element to find.
  • boolean_expression: The predicate used to filter the array elements.
  • first_or_last: Search from the beginning (FIRST) or ending (LAST) of thearray. By default the function searches from the beginning.

Return type

INT64

Examples

The following queries get the offset for the first4 in an array.

SELECTARRAY_OFFSET([1,4,4,4,6],4)ASresult/*--------+ | result | +--------+ | 1      | +--------*/
SELECTARRAY_OFFSET([1,4,4,4,6],4,'FIRST')ASresult/*--------+ | result | +--------+ | 1      | +--------*/

The following queries get the offset for the last4 in an array.

SELECTARRAY_OFFSET([1,4,4,4,6],4,'LAST')ASresult/*--------+ | result | +--------+ | 3      | +--------*/
SELECTARRAY_OFFSET([1,4,4,4,6],e->e=4,'LAST')ASresult/*--------+ | result | +--------+ | 3      | +--------*/

The following query gets the offset for the last element in an array that isgreater than2 and less than5.

SELECTARRAY_OFFSET([1,4,4,4,6],e->e >2ANDe <5,'LAST')ASresult/*--------+ | result | +--------+ | 3      | +--------*/

The following query producesNULL because5 isn't in the array.

SELECTARRAY_OFFSET([1,4,4,4,6],5)ASresult/*--------+ | result | +--------+ | NULL   | +--------*/

The following query producesNULL because there are no elements greaterthan7 in the array.

SELECTARRAY_OFFSET([1,4,4,4,6],e->e >7)ASresult/*--------+ | result | +--------+ | NULL   | +--------*/

ARRAY_OFFSETS

ARRAY_OFFSETS(input_array,element_to_find)element_to_find:{element_expression|element_lambda_expression}element_lambda_expression:element_alias->boolean_expression

Description

Searches an array and gets the zero-based offsets for matching elements.If no matching element is found, returns an empty array.

Arguments:

  • input_array: The array to search.
  • element_expression: The element to find in the array.Must be a comparable data type.
  • element_lambda_expression: Each element ininput_array is evaluatedagainst the lambda expression. If the expression evaluates toTRUE, theelement is included in the search results.
  • element_alias: An alias that represents the element to find.
  • boolean_expression: The predicate used to filter the array elements.

Return type

ARRAY<INT64>

Examples

The following query gets all offsets for4 in an array.

SELECTARRAY_OFFSETS([1,4,4,4,6,4],4)ASresult/*--------------+ | result       | +--------------+ | [1, 2, 3, 5] | +--------------*/

The following query gets the offsets for elements in an array that aregreater than2 and less than5.

SELECTARRAY_OFFSETS([1,4,7,3,6,4],e->e >2ANDe <5)ASresult/*-----------+ | result    | +-----------+ | [1, 3, 5] | +-----------*/

The following query produces an empty array because5 isn't in the array.

SELECTARRAY_OFFSETS([1,4,4,4,6],5)ASresult/*--------+ | result | +--------+ | []     | +--------*/

The following query produces an empty array because there are no elementsgreater than7 in the array.

SELECTARRAY_OFFSETS([1,4,4,4,6],e->e >7)ASresult/*--------+ | result | +--------+ | []     | +--------*/

ARRAY_REVERSE

ARRAY_REVERSE(value)

Description

Returns the inputARRAY with elements in reverse order.

Return type

ARRAY

Examples

SELECTARRAY_REVERSE([1,2,3])ASreverse_arr/*-------------+ | reverse_arr | +-------------+ | [3, 2, 1]   | +-------------*/

ARRAY_SLICE

ARRAY_SLICE(array_to_slice,start_offset,end_offset)

Description

Returns an array containing zero or more consecutive elements from theinput array.

  • array_to_slice: The array that contains the elements you want to slice.
  • start_offset: The inclusive starting offset.
  • end_offset: The inclusive ending offset.

An offset can be positive or negative. A positive offset starts from thebeginning of the input array and is 0-based. A negative offset starts fromthe end of the input array. Out-of-bounds offsets are supported. Here are someexamples:

Input offsetFinal offset in arrayNotes
0['a', 'b', 'c', 'd']The final offset is0.
3['a', 'b', 'c','d']The final offset is3.
5['a', 'b', 'c','d'] Because the input offset is out of bounds, the final offset is3 (array length - 1).
-1['a', 'b', 'c','d'] Because a negative offset is used, the offset starts at the end of the array. The final offset is3 (array length - 1).
-2['a', 'b','c', 'd'] Because a negative offset is used, the offset starts at the end of the array. The final offset is2 (array length - 2).
-4['a', 'b', 'c', 'd'] Because a negative offset is used, the offset starts at the end of the array. The final offset is0 (array length - 4).
-5['a', 'b', 'c', 'd'] Because the offset is negative and out of bounds, the final offset is0 (array length - array length).

Additional details:

  • The input array can containNULL elements.NULL elements are includedin the resulting array.
  • ReturnsNULL ifarray_to_slice,start_offset, orend_offset isNULL.
  • Returns an empty array ifarray_to_slice is empty.
  • Returns an empty array if the position of thestart_offset in the array isafter the position of theend_offset.

Return type

ARRAY

Examples

SELECTARRAY_SLICE(['a','b','c','d','e'],1,3)ASresult/*-----------+ | result    | +-----------+ | [b, c, d] | +-----------*/
SELECTARRAY_SLICE(['a','b','c','d','e'],-1,3)ASresult/*-----------+ | result    | +-----------+ | []        | +-----------*/
SELECTARRAY_SLICE(['a','b','c','d','e'],1,-3)ASresult/*--------+ | result | +--------+ | [b, c] | +--------*/
SELECTARRAY_SLICE(['a','b','c','d','e'],-1,-3)ASresult/*-----------+ | result    | +-----------+ | []        | +-----------*/
SELECTARRAY_SLICE(['a','b','c','d','e'],-3,-1)ASresult/*-----------+ | result    | +-----------+ | [c, d, e] | +-----------*/
SELECTARRAY_SLICE(['a','b','c','d','e'],3,3)ASresult/*--------+ | result | +--------+ | [d]    | +--------*/
SELECTARRAY_SLICE(['a','b','c','d','e'],-3,-3)ASresult/*--------+ | result | +--------+ | [c]    | +--------*/
SELECTARRAY_SLICE(['a','b','c','d','e'],1,30)ASresult/*--------------+ | result       | +--------------+ | [b, c, d, e] | +--------------*/
SELECTARRAY_SLICE(['a','b','c','d','e'],1,-30)ASresult/*-----------+ | result    | +-----------+ | []        | +-----------*/
SELECTARRAY_SLICE(['a','b','c','d','e'],-30,30)ASresult/*-----------------+ | result          | +-----------------+ | [a, b, c, d, e] | +-----------------*/
SELECTARRAY_SLICE(['a','b','c','d','e'],-30,-5)ASresult/*--------+ | result | +--------+ | [a]    | +--------*/
SELECTARRAY_SLICE(['a','b','c','d','e'],5,30)ASresult/*--------+ | result | +--------+ | []     | +--------*/
SELECTARRAY_SLICE(['a','b','c','d','e'],1,NULL)ASresult/*-----------+ | result    | +-----------+ | NULL      | +-----------*/
SELECTARRAY_SLICE(['a','b',NULL,'d','e'],1,3)ASresult/*--------------+ | result       | +--------------+ | [b, NULL, d] | +--------------*/

ARRAY_TO_STRING

ARRAY_TO_STRING(array_expression,delimiter[,null_text])

Description

Returns a concatenation of the elements inarray_expression as aSTRINGorBYTES value. The value forarray_expression caneither be an array ofSTRING orBYTES data type.

If thenull_text parameter is used, the function replaces anyNULL values inthe array with the value ofnull_text.

If thenull_text parameter isn't used, the function omits theNULL valueand its preceding delimiter.

Return type

  • STRING for a function signature withSTRING input.
  • BYTES for a function signature withBYTES input.

Examples

SELECTARRAY_TO_STRING(['coffee','tea','milk',NULL],'--','MISSING')AStext/*--------------------------------+ | text                           | +--------------------------------+ | coffee--tea--milk--MISSING     | +--------------------------------*/
SELECTARRAY_TO_STRING(['cake','pie',NULL],'--','MISSING')AStext/*--------------------------------+ | text                           | +--------------------------------+ | cake--pie--MISSING             | +--------------------------------*/
SELECTARRAY_TO_STRING([b'prefix',b'middle',b'suffix',b'\x00'],b'--')ASdata/*--------------------------------+ | data                           | +--------------------------------+ | prefix--middle--suffix--\x00   | +--------------------------------*/

ARRAY_TRANSFORM

ARRAY_TRANSFORM(array_expression,lambda_expression)lambda_expression:{element_alias->transform_expression|(element_alias,index_alias)->transform_expression}

Description

Takes an array, transforms the elements, and returns the results in a new array.The output array always has the same length as the input array.

ReturnsNULL if thearray_expression isNULL.

Return type

ARRAY

Example

SELECTARRAY_TRANSFORM([1,4,3],e->e+1)ASa1,ARRAY_TRANSFORM([1,4,3],(e,i)->e+i)ASa2;/*---------+---------+ | a1      | a2      | +---------+---------+ | [2,5,4] | [1,5,5] | +---------+---------*/

GENERATE_ARRAY

GENERATE_ARRAY(start_expression,end_expression[,step_expression])

Description

Returns an array of values. Thestart_expression andend_expressionparameters determine the inclusive start and end of the array.

TheGENERATE_ARRAY function accepts the following data types as inputs:

  • INT64
  • FLOAT64

Thestep_expression parameter determines the increment used togenerate array values. The default value for this parameter is1.

This function returns an error ifstep_expression is set to 0, or if anyinput isNaN.

If any argument isNULL, the function will return aNULL array.

Return Data Type

ARRAY

Examples

The following returns an array of integers, with a default step of 1.

SELECTGENERATE_ARRAY(1,5)ASexample_array;/*-----------------+ | example_array   | +-----------------+ | [1, 2, 3, 4, 5] | +-----------------*/

The following returns an array using a user-specified step size.

SELECTGENERATE_ARRAY(0,10,3)ASexample_array;/*---------------+ | example_array | +---------------+ | [0, 3, 6, 9]  | +---------------*/

The following returns an array using a negative value,-3 for its step size.

SELECTGENERATE_ARRAY(10,0,-3)ASexample_array;/*---------------+ | example_array | +---------------+ | [10, 7, 4, 1] | +---------------*/

The following returns an array using the same value for thestart_expressionandend_expression.

SELECTGENERATE_ARRAY(4,4,10)ASexample_array;/*---------------+ | example_array | +---------------+ | [4]           | +---------------*/

The following returns an empty array, because thestart_expression is greaterthan theend_expression, and thestep_expression value is positive.

SELECTGENERATE_ARRAY(10,0,3)ASexample_array;/*---------------+ | example_array | +---------------+ | []            | +---------------*/

The following returns aNULL array becauseend_expression isNULL.

SELECTGENERATE_ARRAY(5,NULL,1)ASexample_array;/*---------------+ | example_array | +---------------+ | NULL          | +---------------*/

The following returns multiple arrays.

SELECTGENERATE_ARRAY(start,5)ASexample_arrayFROMUNNEST([3,4,5])ASstart;/*---------------+ | example_array | +---------------+ | [3, 4, 5]     | | [4, 5]        | | [5]           | +---------------*/

GENERATE_DATE_ARRAY

GENERATE_DATE_ARRAY(start_date,end_date[,INTERVALINT64_exprdate_part])

Description

Returns an array of dates. Thestart_date andend_dateparameters determine the inclusive start and end of the array.

TheGENERATE_DATE_ARRAY function accepts the following data types as inputs:

  • start_date must be aDATE.
  • end_date must be aDATE.
  • INT64_expr must be anINT64.
  • date_part must be either DAY, WEEK, MONTH, QUARTER, or YEAR.

TheINT64_expr parameter determines the increment used to generate dates. Thedefault value for this parameter is 1 day.

This function returns an error ifINT64_expr is set to 0.

Return Data Type

ARRAY containing 0 or moreDATE values.

Examples

The following returns an array of dates, with a default step of 1.

SELECTGENERATE_DATE_ARRAY('2016-10-05','2016-10-08')ASexample;/*--------------------------------------------------+ | example                                          | +--------------------------------------------------+ | [2016-10-05, 2016-10-06, 2016-10-07, 2016-10-08] | +--------------------------------------------------*/

The following returns an array using a user-specified step size.

SELECTGENERATE_DATE_ARRAY('2016-10-05','2016-10-09',INTERVAL2DAY)ASexample;/*--------------------------------------+ | example                              | +--------------------------------------+ | [2016-10-05, 2016-10-07, 2016-10-09] | +--------------------------------------*/

The following returns an array using a negative value,-3 for its step size.

SELECTGENERATE_DATE_ARRAY('2016-10-05','2016-10-01',INTERVAL-3DAY)ASexample;/*--------------------------+ | example                  | +--------------------------+ | [2016-10-05, 2016-10-02] | +--------------------------*/

The following returns an array using the same value for thestart_dateandend_date.

SELECTGENERATE_DATE_ARRAY('2016-10-05','2016-10-05',INTERVAL8DAY)ASexample;/*--------------+ | example      | +--------------+ | [2016-10-05] | +--------------*/

The following returns an empty array, because thestart_date is greaterthan theend_date, and thestep value is positive.

SELECTGENERATE_DATE_ARRAY('2016-10-05','2016-10-01',INTERVAL1DAY)ASexample;/*---------+ | example | +---------+ | []      | +---------*/

The following returns aNULL array, because one of its inputs isNULL.

SELECTGENERATE_DATE_ARRAY('2016-10-05',NULL)ASexample;/*---------+ | example | +---------+ | NULL    | +---------*/

The following returns an array of dates, using MONTH as thedate_partinterval:

SELECTGENERATE_DATE_ARRAY('2016-01-01','2016-12-31',INTERVAL2MONTH)ASexample;/*--------------------------------------------------------------------------+ | example                                                                  | +--------------------------------------------------------------------------+ | [2016-01-01, 2016-03-01, 2016-05-01, 2016-07-01, 2016-09-01, 2016-11-01] | +--------------------------------------------------------------------------*/

The following uses non-constant dates to generate an array.

SELECTGENERATE_DATE_ARRAY(date_start,date_end,INTERVAL1WEEK)ASdate_rangeFROM(SELECTDATE'2016-01-01'ASdate_start,DATE'2016-01-31'ASdate_endUNIONALLSELECTDATE"2016-04-01",DATE"2016-04-30"UNIONALLSELECTDATE"2016-07-01",DATE"2016-07-31"UNIONALLSELECTDATE"2016-10-01",DATE"2016-10-31")ASitems;/*--------------------------------------------------------------+ | date_range                                                   | +--------------------------------------------------------------+ | [2016-01-01, 2016-01-08, 2016-01-15, 2016-01-22, 2016-01-29] | | [2016-04-01, 2016-04-08, 2016-04-15, 2016-04-22, 2016-04-29] | | [2016-07-01, 2016-07-08, 2016-07-15, 2016-07-22, 2016-07-29] | | [2016-10-01, 2016-10-08, 2016-10-15, 2016-10-22, 2016-10-29] | +--------------------------------------------------------------*/

GENERATE_TIMESTAMP_ARRAY

GENERATE_TIMESTAMP_ARRAY(start_timestamp,end_timestamp,INTERVALstep_expressiondate_part)

Description

Returns anARRAY ofTIMESTAMPS separated by a given interval. Thestart_timestamp andend_timestamp parameters determine the inclusivelower and upper bounds of theARRAY.

TheGENERATE_TIMESTAMP_ARRAY function accepts the following data types asinputs:

  • start_timestamp:TIMESTAMP
  • end_timestamp:TIMESTAMP
  • step_expression:INT64
  • Alloweddate_part values are:MICROSECOND,MILLISECOND,SECOND,MINUTE,HOUR, orDAY.

Thestep_expression parameter determines the increment used to generatetimestamps.

Return Data Type

AnARRAY containing 0 or moreTIMESTAMP values.

Examples

The following example returns anARRAY ofTIMESTAMPs at intervals of 1 day.

SELECTGENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00','2016-10-07 00:00:00',INTERVAL1DAY)AStimestamp_array;/*--------------------------------------------------------------------------+ | timestamp_array                                                          | +--------------------------------------------------------------------------+ | [2016-10-05 00:00:00+00, 2016-10-06 00:00:00+00, 2016-10-07 00:00:00+00] | +--------------------------------------------------------------------------*/

The following example returns anARRAY ofTIMESTAMPs at intervals of 1second.

SELECTGENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00','2016-10-05 00:00:02',INTERVAL1SECOND)AStimestamp_array;/*--------------------------------------------------------------------------+ | timestamp_array                                                          | +--------------------------------------------------------------------------+ | [2016-10-05 00:00:00+00, 2016-10-05 00:00:01+00, 2016-10-05 00:00:02+00] | +--------------------------------------------------------------------------*/

The following example returns anARRAY ofTIMESTAMPS with a negativeinterval.

SELECTGENERATE_TIMESTAMP_ARRAY('2016-10-06 00:00:00','2016-10-01 00:00:00',INTERVAL-2DAY)AStimestamp_array;/*--------------------------------------------------------------------------+ | timestamp_array                                                          | +--------------------------------------------------------------------------+ | [2016-10-06 00:00:00+00, 2016-10-04 00:00:00+00, 2016-10-02 00:00:00+00] | +--------------------------------------------------------------------------*/

The following example returns anARRAY with a single element, becausestart_timestamp andend_timestamp have the same value.

SELECTGENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00','2016-10-05 00:00:00',INTERVAL1HOUR)AStimestamp_array;/*--------------------------+ | timestamp_array          | +--------------------------+ | [2016-10-05 00:00:00+00] | +--------------------------*/

The following example returns an emptyARRAY, becausestart_timestamp islater thanend_timestamp.

SELECTGENERATE_TIMESTAMP_ARRAY('2016-10-06 00:00:00','2016-10-05 00:00:00',INTERVAL1HOUR)AStimestamp_array;/*-----------------+ | timestamp_array | +-----------------+ | []              | +-----------------*/

The following example returns a nullARRAY, because one of the inputs isNULL.

SELECTGENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00',NULL,INTERVAL1HOUR)AStimestamp_array;/*-----------------+ | timestamp_array | +-----------------+ | NULL            | +-----------------*/

Supplemental materials

OFFSET and ORDINAL

For information about usingOFFSET andORDINAL with arrays, seeArray subscript operator andAccessing arrayelements.

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-15 UTC.