Range functions

GoogleSQL for BigQuery supports the following range functions.

Function list

NameSummary
APPENDS Returns all rows appended to a table for a given time range.
For more information, seeTime series functions.
CHANGES Returns all rows that have changed in a table for a given time range.
For more information, seeTime series functions.
GENERATE_RANGE_ARRAY Splits a range into an array of subranges.
RANGE Constructs a range ofDATE,DATETIME, orTIMESTAMP values.
RANGE_BUCKET Scans through a sorted array and returns the 0-based position of a point's upper bound.
For more information, seeMathematical functions.
RANGE_CONTAINS Signature 1: Checks if one range is in another range.

Signature 2: Checks if a value is in a range.
RANGE_ENDGets the upper bound of a range.
RANGE_INTERSECTGets a segment of two ranges that intersect.
RANGE_OVERLAPSChecks if two ranges overlap.
RANGE_SESSIONIZE Produces a table of sessionized ranges.
RANGE_STARTGets the lower bound of a range.

GENERATE_RANGE_ARRAY

GENERATE_RANGE_ARRAY(range_to_split,step_interval)
GENERATE_RANGE_ARRAY(range_to_split,step_interval,include_last_partial_range)

Description

Splits a range into an array of subranges.

Definitions

  • range_to_split: TheRANGE<T> value to split.

  • step_interval: TheINTERVAL value, which determines the maximum size ofeach subrange in the resulting array. Aninterval single date and time partis supported, but an interval range of date and time parts isn't.

    • Ifrange_to_split isRANGE<DATE>, these intervaldate parts are supported:YEAR toDAY.

    • Ifrange_to_split isRANGE<DATETIME>, these intervaldate and time parts are supported:YEAR toSECOND.

    • Ifrange_to_split isRANGE<TIMESTAMP>, these intervaldate and time parts are supported:DAY toSECOND.

  • include_last_partial_range: ABOOL value, which determines whether ornot to include the last subrange if it's a partial subrange.If this argument isn't specified, the default value isTRUE.

    • TRUE (default): The last subrange is included, even if it'ssmaller thanstep_interval.

    • FALSE: Exclude the last subrange if it's smaller thanstep_interval.

Details

ReturnsNULL if any input isNULL.

Return type

ARRAY<RANGE<T>>

Examples

In the following example, a date range between2020-01-01 and2020-01-06is split into an array of subranges that are one day long. There areno partial ranges.

SELECTGENERATE_RANGE_ARRAY(RANGE(DATE'2020-01-01',DATE'2020-01-06'),INTERVAL1DAY)ASresults;/*----------------------------+ | results                    | +----------------------------+ | [                          | |  [2020-01-01, 2020-01-02), | |  [2020-01-02, 2020-01-03), | |  [2020-01-03, 2020-01-04), | |  [2020-01-04, 2020-01-05), | |  [2020-01-05, 2020-01-06), | | ]                          | +----------------------------*/

In the following examples, a date range between2020-01-01 and2020-01-06is split into an array of subranges that are two days long. The final subrangeis smaller than two days:

SELECTGENERATE_RANGE_ARRAY(RANGE(DATE'2020-01-01',DATE'2020-01-06'),INTERVAL2DAY)ASresults;/*----------------------------+ | results                    | +----------------------------+ | [                          | |  [2020-01-01, 2020-01-03), | |  [2020-01-03, 2020-01-05), | |  [2020-01-05, 2020-01-06)  | | ]                          | +----------------------------*/
SELECTGENERATE_RANGE_ARRAY(RANGE(DATE'2020-01-01',DATE'2020-01-06'),INTERVAL2DAY,TRUE)ASresults;/*----------------------------+ | results                    | +----------------------------+ | [                          | |  [2020-01-01, 2020-01-03), | |  [2020-01-03, 2020-01-05), | |  [2020-01-05, 2020-01-06)  | | ]                          | +----------------------------*/

In the following example, a date range between2020-01-01 and2020-01-06is split into an array of subranges that are two days long, but the finalsubrange is excluded because it's smaller than two days:

SELECTGENERATE_RANGE_ARRAY(RANGE(DATE'2020-01-01',DATE'2020-01-06'),INTERVAL2DAY,FALSE)ASresults;/*----------------------------+ | results                    | +----------------------------+ | [                          | |  [2020-01-01, 2020-01-03), | |  [2020-01-03, 2020-01-05)  | | ]                          | +----------------------------*/

RANGE

RANGE(lower_bound,upper_bound)

Description

Constructs a range ofDATE,DATETIME, orTIMESTAMP values.

Definitions

  • lower_bound: The range starts from this value. This can be aDATE,DATETIME, orTIMESTAMP value. If this value isNULL, the rangedoesn't include a lower bound.
  • upper_bound: The range ends before this value. This can be aDATE,DATETIME, orTIMESTAMP value. If this value isNULL, the rangedoesn't include an upper bound.

Details

lower_bound andupper_bound must be of the same data type.

Produces an error iflower_bound is greater than or equal toupper_bound.To returnNULL instead, add theSAFE. prefix to the function name.

Return type

RANGE<T>, whereT is the same data type as the input.

Examples

The following query constructs a date range:

SELECTRANGE(DATE'2022-12-01',DATE'2022-12-31')ASresults;/*--------------------------+ | results                  | +--------------------------+ | [2022-12-01, 2022-12-31) | +--------------------------*/

The following query constructs a datetime range:

SELECTRANGE(DATETIME'2022-10-01 14:53:27',DATETIME'2022-10-01 16:00:00')ASresults;/*---------------------------------------------+ | results                                     | +---------------------------------------------+ | [2022-10-01T14:53:27, 2022-10-01T16:00:00)  | +---------------------------------------------*/

The following query constructs a timestamp range:

SELECTRANGE(TIMESTAMP'2022-10-01 14:53:27 America/Los_Angeles',TIMESTAMP'2022-10-01 16:00:00 America/Los_Angeles')ASresults;-- Results depend upon where this query was executed./*------------------------------------------------------------------+ | results                                                          | +------------------------------------------------------------------+ | [2022-10-01 21:53:27.000000 UTC, 2022-10-01 23:00:00.000000 UTC) | +------------------------------------------------------------------*/

The following query constructs a date range with no lower bound:

SELECTRANGE(NULL,DATE'2022-12-31')ASresults;/*-------------------------+ | results                 | +-------------------------+ | [UNBOUNDED, 2022-12-31) | +-------------------------*/

The following query constructs a date range with no upper bound:

SELECTRANGE(DATE'2022-10-01',NULL)ASresults;/*--------------------------+ | results                  | +--------------------------+ | [2022-10-01, UNBOUNDED)  | +--------------------------*/

RANGE_CONTAINS

  • Signature 1: Checks if every value in one range isin another range.
  • Signature 2: Checks if a value is in a range.

Signature 1

RANGE_CONTAINS(outer_range,inner_range)

Description

Checks if the inner range is in the outer range.

Definitions

  • outer_range: TheRANGE<T> value to search within.
  • inner_range: TheRANGE<T> value to search for inouter_range.

Details

ReturnsTRUE ifinner_range exists inouter_range.Otherwise, returnsFALSE.

T must be of the same type for all inputs.

Return type

BOOL

Examples

In the following query, the inner range is in the outer range:

SELECTRANGE_CONTAINS(RANGE<DATE>'[2022-01-01, 2023-01-01)',RANGE<DATE>'[2022-04-01, 2022-07-01)')ASresults;/*---------+ | results | +---------+ | TRUE    | +---------*/

In the following query, the inner range isn't in the outer range:

SELECTRANGE_CONTAINS(RANGE<DATE>'[2022-01-01, 2023-01-01)',RANGE<DATE>'[2023-01-01, 2023-04-01)')ASresults;/*---------+ | results | +---------+ | FALSE   | +---------*/

Signature 2

RANGE_CONTAINS(range_to_search,value_to_find)

Description

Checks if a value is in a range.

Definitions

  • range_to_search: TheRANGE<T> value to search within.
  • value_to_find: The value to search for inrange_to_search.

Details

ReturnsTRUE ifvalue_to_find exists inrange_to_search.Otherwise, returnsFALSE.

The data type forvalue_to_find must be the same data type asTinrange_to_search.

Return type

BOOL

Examples

In the following query, the value2022-04-01 is found in the range[2022-01-01, 2023-01-01):

SELECTRANGE_CONTAINS(RANGE<DATE>'[2022-01-01, 2023-01-01)',DATE'2022-04-01')ASresults;/*---------+ | results | +---------+ | TRUE    | +---------*/

In the following query, the value2023-04-01 isn't found in the range[2022-01-01, 2023-01-01):

SELECTRANGE_CONTAINS(RANGE<DATE>'[2022-01-01, 2023-01-01)',DATE'2023-04-01')ASresults;/*---------+ | results | +---------+ | FALSE   | +---------*/

RANGE_END

RANGE_END(range_to_check)

Description

Gets the upper bound of a range.

Definitions

  • range_to_check: TheRANGE<T> value.

Details

ReturnsNULL if the upper bound inrange_value isUNBOUNDED.

ReturnsNULL ifrange_to_check isNULL.

Return type

T inrange_value

Examples

In the following query, the upper bound of the range is retrieved:

SELECTRANGE_END(RANGE<DATE>'[2022-12-01, 2022-12-31)')ASresults;/*------------+ | results    | +------------+ | 2022-12-31 | +------------*/

In the following query, the upper bound of the range is unbounded, soNULL is returned:

SELECTRANGE_END(RANGE<DATE>'[2022-12-01, UNBOUNDED)')ASresults;/*------------+ | results    | +------------+ | NULL       | +------------*/

RANGE_INTERSECT

RANGE_INTERSECT(range_a,range_b)

Description

Gets a segment of two ranges that intersect.

Definitions

  • range_a: The firstRANGE<T> value.
  • range_b: The secondRANGE<T> value.

Details

ReturnsNULL if any input isNULL.

Produces an error ifrange_a andrange_b don't overlap. To returnNULL instead, add theSAFE. prefix to the function name.

T must be of the same type for all inputs.

Return type

RANGE<T>

Examples

SELECTRANGE_INTERSECT(RANGE<DATE>'[2022-02-01, 2022-09-01)',RANGE<DATE>'[2021-06-15, 2022-04-15)')ASresults;/*--------------------------+ | results                  | +--------------------------+ | [2022-02-01, 2022-04-15) | +--------------------------*/
SELECTRANGE_INTERSECT(RANGE<DATE>'[2022-02-01, UNBOUNDED)',RANGE<DATE>'[2021-06-15, 2022-04-15)')ASresults;/*--------------------------+ | results                  | +--------------------------+ | [2022-02-01, 2022-04-15) | +--------------------------*/
SELECTRANGE_INTERSECT(RANGE<DATE>'[2022-02-01, UNBOUNDED)',RANGE<DATE>'[2021-06-15, UNBOUNDED)')ASresults;/*-------------------------+ | results                 | +-------------------------+ | [2022-02-01, UNBOUNDED) | +-------------------------*/

RANGE_OVERLAPS

RANGE_OVERLAPS(range_a,range_b)

Description

Checks if two ranges overlap.

Definitions

  • range_a: The firstRANGE<T> value.
  • range_b: The secondRANGE<T> value.

Details

ReturnsTRUE if a part ofrange_a intersects withrange_b, otherwisereturnsFALSE.

T must be of the same type for all inputs.

To get the part of the range that overlaps, use theRANGE_INTERSECT function.

Return type

BOOL

Examples

In the following query, the first and second ranges overlap between2022-02-01 and2022-04-15:

SELECTRANGE_OVERLAPS(RANGE<DATE>'[2022-02-01, 2022-09-01)',RANGE<DATE>'[2021-06-15, 2022-04-15)')ASresults;/*---------+ | results | +---------+ | TRUE    | +---------*/

In the following query, the first and second ranges don't overlap:

SELECTRANGE_OVERLAPS(RANGE<DATE>'[2020-02-01, 2020-09-01)',RANGE<DATE>'[2021-06-15, 2022-04-15)')ASresults;/*---------+ | results | +---------+ | FALSE   | +---------*/

In the following query, the first and second ranges overlap between2022-02-01 andUNBOUNDED:

SELECTRANGE_OVERLAPS(RANGE<DATE>'[2022-02-01, UNBOUNDED)',RANGE<DATE>'[2021-06-15, UNBOUNDED)')ASresults;/*---------+ | results | +---------+ | TRUE    | +---------*/

RANGE_SESSIONIZE

RANGE_SESSIONIZE(TABLEtable_name,range_column,partitioning_columns)
RANGE_SESSIONIZE(TABLEtable_name,range_column,partitioning_columns,sessionize_option)

Description

Produces a table of sessionized ranges.

Definitions

  • table_name: A table expression that represents the name of the table toconstruct. This can represent any relation withrange_column.
  • range_column: ASTRING literal that indicates whichRANGE columnin a table contains the data to sessionize.
  • partitioning_columns: AnARRAY<STRING> literal that indicates whichcolumns should partition the data before the data is sessionized.

  • sessionize_option: ASTRING value that describes how order-adjacentranges are sessionized. Your choices are as follows:

    • MEETS (default): Ranges that meet or overlap are sessionized.

    • OVERLAPS: Only a range that's overlapped by another range issessionized.

    If this argument isn't provided,MEETS is used by default.

Details

This function produces a table that includes all columns in theinput table and an additionalRANGE column calledsession_range, which indicates the start and end of a session. Thestart and end of each session is determined by thesessionize_optionargument.

Return type

TABLE

Examples

The examples in this section reference the following table calledmy_sessionized_range_table in a dataset calledmydataset:

INSERTmydataset.my_sessionized_range_table(emp_id,dept_id,duration)VALUES(10,1000,RANGE<DATE>'[2010-01-10, 2010-03-10)'),(10,2000,RANGE<DATE>'[2010-03-10, 2010-07-15)'),(10,2000,RANGE<DATE>'[2010-06-15, 2010-08-18)'),(20,2000,RANGE<DATE>'[2010-03-10, 2010-07-20)'),(20,1000,RANGE<DATE>'[2020-05-10, 2020-09-20)');SELECT*FROMmydataset.my_sessionized_range_tableORDERBYemp_id;/*--------+---------+--------------------------+ | emp_id | dept_id | duration                 | +--------+---------+--------------------------+ | 10     | 1000    | [2010-01-10, 2010-03-10) | | 10     | 2000    | [2010-03-10, 2010-07-15) | | 10     | 2000    | [2010-06-15, 2010-08-18) | | 20     | 2000    | [2010-03-10, 2010-07-20) | | 20     | 1000    | [2020-05-10, 2020-09-20) | +--------+---------+--------------------------*/

In the following query, a table of sessionized data is produced formy_sessionized_range_table, and only ranges that meet or overlap aresessionized:

SELECTemp_id,duration,session_rangeFROMRANGE_SESSIONIZE(TABLEmydataset.my_sessionized_range_table,'duration',['emp_id'])ORDERBYemp_id;/*--------+--------------------------+--------------------------+ | emp_id | duration                 | session_range            | +--------+--------------------------+--------------------------+ | 10     | [2010-01-10, 2010-03-10) | [2010-01-10, 2010-08-18) | | 10     | [2010-03-10, 2010-07-15) | [2010-01-10, 2010-08-18) | | 10     | [2010-06-15, 2010-08-18) | [2010-01-10, 2010-08-18) | | 20     | [2010-03-10, 2010-07-20) | [2010-03-10, 2010-07-20) | | 20     | [2020-05-10, 2020-09-20) | [2020-05-10, 2020-09-20) | +--------+-----------------------------------------------------*/

In the following query, a table of sessionized data is produced formy_sessionized_range_table, and only a range that's overlapped by anotherrange is sessionized:

SELECTemp_id,duration,session_rangeFROMRANGE_SESSIONIZE(TABLEmydataset.my_sessionized_range_table,'duration',['emp_id'],'OVERLAPS')ORDERBYemp_id;/*--------+--------------------------+--------------------------+ | emp_id | duration                 | session_range            | +--------+--------------------------+--------------------------+ | 10     | [2010-03-10, 2010-07-15) | [2010-03-10, 2010-08-18) | | 10     | [2010-06-15, 2010-08-18) | [2010-03-10, 2010-08-18) | | 10     | [2010-01-10, 2010-03-10) | [2010-01-10, 2010-03-10) | | 20     | [2020-05-10, 2020-09-20) | [2020-05-10, 2020-09-20) | | 20     | [2010-03-10, 2010-07-20) | [2010-03-10, 2010-07-20) | +--------+-----------------------------------------------------*/

If you need to normalize sessionized data, you can use a query similar to thefollowing:

SELECTemp_id,session_rangeASnormalizedFROM(SELECTemp_id,session_rangeFROMRANGE_SESSIONIZE(TABLEmydataset.my_sessionized_range_table,'duration',['emp_id'],'MEETS'))GROUPBYemp_id,normalized;/*--------+--------------------------+ | emp_id | normalized               | +--------+--------------------------+ | 20     | [2010-03-10, 2010-07-20) | | 10     | [2010-01-10, 2010-08-18) | | 20     | [2020-05-10, 2020-09-20) | +--------+--------------------------*/

RANGE_START

RANGE_START(range_to_check)

Description

Gets the lower bound of a range.

Definitions

  • range_to_check: TheRANGE<T> value.

Details

ReturnsNULL if the lower bound ofrange_value isUNBOUNDED.

ReturnsNULL ifrange_to_check isNULL.

Return type

T inrange_value

Examples

In the following query, the lower bound of the range is retrieved:

SELECTRANGE_START(RANGE<DATE>'[2022-12-01, 2022-12-31)')ASresults;/*------------+ | results    | +------------+ | 2022-12-01 | +------------*/

In the following query, the lower bound of the range is unbounded, soNULL is returned:

SELECTRANGE_START(RANGE<DATE>'[UNBOUNDED, 2022-12-31)')ASresults;/*------------+ | results    | +------------+ | NULL       | +------------*/

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-11-24 UTC.