Time series functions

GoogleSQL for BigQuery supports the following time series functions.

Function list

NameSummary
APPENDS Returns all rows appended to a table for a given time range.
CHANGES Returns all rows that have changed in a table for a given time range.
DATE_BUCKET Gets the lower bound of the date bucket that contains a date.
DATETIME_BUCKET Gets the lower bound of the datetime bucket that contains a datetime.
GAP_FILL Finds and fills gaps in a time series.
TIMESTAMP_BUCKET Gets the lower bound of the timestamp bucket that contains a timestamp.

APPENDS

Preview

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

Note: To provide feedback or request support for this feature, send an email tobq-change-history-feedback@google.com.
APPENDS(TABLEtable,start_timestampDEFAULTNULL,end_timestampDEFAULTNULL)

Description

TheAPPENDS function returns all rows appended to a table for a giventime range.

The following operations add rows to theAPPENDS change history:

Definitions

  • table: the BigQuery table name. This must be a regularBigQuery table. This argument must be preceded by the wordTABLE.
  • start_timestamp: aTIMESTAMPvalue indicating the earliest time at which achange is included in the output. If the value isNULL, all changes since thetable creation are returned. If the table was created after thestart_timestamp value, the actual table creation time is used instead. An erroris returned if the time specified is earlier than allowed bytime travel, orif the table was created earlier than allowed by time travel if thestart_timestamp value isNULL. For standard tables, this window is seven days,but you canconfigure the time travel window to be less thanthat.

  • end_timestamp: aTIMESTAMP value indicating the latest time atwhich a change is included in the output.end_timestamp is exclusive; forexample, if you specify2023-12-31 08:00:00 forstart_timestamp and2023-12-31 12:00:00 forend_timestamp, all changes made from8 AM December 31, 2023 through 11:59 AM December 31, 2023 are returned.

    If theend_timestamp value isNULL, all changes madeuntil the start of the query are included.

Details

Records of inserted rows persist even if that data is later deleted. Deletionsaren't reflected in theAPPENDS function. If a tableis copied, calling theAPPENDS function on the copied table returns every rowas inserted at the time of table creation. If a row is modified due to anUPDATE operation, there's no effect.

Output

TheAPPENDS function returns a table with the following columns:

  • All columns of the input table at the time the query is run. If a column isadded after theend_timestamp value, it appears withNULL values populated in anyof the rows that were inserted before the addition of the column.
  • _CHANGE_TYPE: aSTRING value indicating the type of change that producedthe row. ForAPPENDS, the only supported value isINSERT.
  • _CHANGE_TIMESTAMP: aTIMESTAMP value indicating the commit time of thetransaction that made the change.

Limitations

  • The data returned by theAPPENDS function is limited to the time travelwindow of the table.
  • The data returned by theAPPENDS function is limited to the table's currentschema.
  • You can't call theAPPENDS function within a multi-statement transaction.
  • APPENDS function may not capture all rows appended within a multi-statementtransaction if some appended rows are updated or deleted within the sametransaction.
  • You can only use theAPPENDS function with regular BigQuerytables. Clones, snapshots, views, materialized views, external tables, andwildcard tables aren't supported.
  • Partition pseudo-columns for ingestion-time partitioned tables, such as_PARTITIONTIME and_PARTITIONDATE, aren't included in the function'soutput.

Example

This example shows the change history returned by theAPPENDS function as variouschanges are made to a table calledProduce. First, create the table:

CREATETABLEmydataset.Produce(productSTRING,inventoryINT64)AS(SELECT'apples'ASproduct,10ASinventory);

Next, insert two rows into the table:

INSERTINTOmydataset.ProduceVALUES('bananas',20),('carrots',30);

To view the full change history of appends, useNULL values to get the fullhistory within the time travel window:

SELECTproduct,inventory,_CHANGE_TYPEASchange_type,_CHANGE_TIMESTAMPASchange_timeFROMAPPENDS(TABLEmydataset.Produce,NULL,NULL);

The output is similar to the following:

+---------+-----------+-------------+--------------------------------+| product | inventory | change_type | change_time                    |+---------+-----------+-------------+--------------------------------+| apples  | 10        | INSERT      | 2022-04-15 20:06:00.488000 UTC || bananas | 20        | INSERT      | 2022-04-15 20:06:08.490000 UTC || carrots | 30        | INSERT      | 2022-04-15 20:06:08.490000 UTC |+---------+-----------+-------------+--------------------------------+

Next, add a column, insert a new row of values, update the inventory, and deletethebananas row:

ALTERTABLEmydataset.ProduceADDCOLUMNcolorSTRING;INSERTINTOmydataset.ProduceVALUES('grapes',40,'purple');UPDATEmydataset.ProduceSETinventory=inventory+5WHERETRUE;DELETEmydataset.ProduceWHEREproduct='bananas';

View the new table:

SELECT*FROMmydataset.Produce;

The output is similar to the following:

+---------+-----------+--------+| product | inventory | color  |+---------+-----------+--------+| apples  | 15        | NULL   || carrots | 35        | NULL   || grapes  | 45        | purple |+---------+-----------+--------+

View the full change history of appends:

SELECTproduct,inventory,color,_CHANGE_TYPEASchange_type,_CHANGE_TIMESTAMPASchange_timeFROMAPPENDS(TABLEmydataset.Produce,NULL,NULL);

The output is similar to the following:

+---------+-----------+--------+-------------+--------------------------------+| product | inventory | color  | change_type | change_time                    |+---------+-----------+--------+-------------+--------------------------------+| apples  | 10        | NULL   | INSERT      | 2022-04-15 20:06:00.488000 UTC || bananas | 20        | NULL   | INSERT      | 2022-04-15 20:06:08.490000 UTC || carrots | 30        | NULL   | INSERT      | 2022-04-15 20:06:08.490000 UTC || grapes  | 40        | purple | INSERT      | 2022-04-15 20:07:45.751000 UTC |+---------+-----------+--------+-------------+--------------------------------+

Theinventory column displays the values that were set when the rows wereoriginally inserted into to the table. It doesn't show the changes from theUPDATE statement. The row with information on bananas is still present becausetheAPPENDS function only captures additions to tables, not deletions.

CHANGES

Preview

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

Note: To provide feedback or request support for this feature, send an email tobq-change-history-feedback@google.com.
CHANGES(TABLEtable,start_timestampDEFAULTNULL,end_timestamp)

Description

TheCHANGES function returns all rows that have changed in a table for a giventime range. To use theCHANGES function on a table, you must set the table'senable_change_history optiontoTRUE.

The following operations add rows to theCHANGES change history:

Definitions

  • table: the BigQuery table name. This must be a regularBigQuery table, and must have theenable_change_historyoption set toTRUE. Enabling this table option has animpact on costs; for more information seePricing and costs. Thisargument must be preceded by the wordTABLE.
  • start_timestamp: aTIMESTAMP value indicating the earliesttime at which a change is included in the output. If the value isNULL,all changes since the table creation are returned. If you set theenable_change_history option after setting thestart_timestamp option,the history before the enablement time might be incomplete. If the table wascreated after thestart_timestamp value, the actual table creation time isused instead. An error is returned if the time specified is earlier thanallowed bytime travel, or if the table was created earlierthan allowed by time travel if thestart_timestamp value isNULL. Forstandard tables, this window is seven days, but you canconfigure the timetravel window to be less than that.
  • end_timestamp: aTIMESTAMP value indicating the latest time at which achange is included in the output.end_timestamp is exclusive; for example,if you specify2023-12-31 08:00:00 forstart_timestamp and2023-12-3112:00:00 forend_timestamp, all changes made from 8 AM December 31, 2023through 11:59 AM December 31, 2023 are returned. The maximum time rangeallowed betweenstart_timestamp andend_timestamp is one day. For abatch query,end_timestamp must be at least ten minutes prior to thecurrent time. For acontinuous query, set this asNULLto return the latest data.

Details

If a row is inserted, a record of the new row with anINSERT change type isproduced.

If a row is deleted, a record of the deleted row with aDELETE change type isproduced.

If a row is updated, a record of the old row with aDELETE change type and arecord of the new row with anUPDATE change type are produced.

Output

TheCHANGES function returns a table with the following columns:

  • All columns of the input table at the time that the query is run. If acolumn is added after theend_timestamp value, it appears withNULL valuespopulated in of the any rows that were changed before the addition ofthe column.
  • _CHANGE_TYPE: aSTRING value indicating the type of change that producedthe row. ForCHANGES, the supported values areINSERT,UPDATE, andDELETE.
  • _CHANGE_TIMESTAMP: aTIMESTAMP value indicating the commit time of thetransaction that made the change.

Limitations

  • The data returned by theCHANGES function is limited to the timetravel window of the table.
  • The data returned by theCHANGES function is limited to the table's currentschema.
  • The maximum allowed time range between thestart_timestamp andend_timestamp arguments you specify for the function is one day.
  • TheCHANGES function can't query the last ten minutes of table history.Therefore, theend_timestamp argument value must be at least ten minutesprior to the current time.
  • You can't call theCHANGES function within a multi-statement transaction.
  • You can't use theCHANGES function with tables that have had multi-statementtransactions committed to them within the requested time window.
  • You can only use theCHANGES function with regular BigQuery tables.Views, materialized views, external tables, and wildcard tables aren'tsupported.
  • For tables that have been cloned or snapshotted, and for tables that arerestored from a clone or snapshot, change history from the source table isn'tcarried over to the new table, clone, or snapshot.
  • You can't use theCHANGES function with a table that haschange data capture enabled.
  • Partition pseudo-columns for ingestion-time partitioned tables, such as_PARTITIONTIME and_PARTITIONDATE, aren't included in the function'soutput.
  • Change history isn't captured for table deletions made due to table partitionexpiration.
  • Performingdata manipulation language (DML) statements over recently streamed datafails on tables that have theenable_change_history option set toTRUE.

Example

This example shows the change history returned by theCHANGES function asvarious changes are made to a table calledProduce. First, create the table:

CREATETABLEmydataset.Produce(productSTRING,inventoryINT64)OPTIONS(enable_change_history=true);

Insert two rows into the table:

INSERTINTOmydataset.ProduceVALUES('bananas',20),('carrots',30);

Delete one row from the table:

DELETEmydataset.ProduceWHEREproduct='bananas';

Update one row of the table:

UPDATEmydataset.ProduceSETinventory=inventory-10WHEREproduct='carrots';

Wait for 10 minutes and view the full change history of the changes:

SELECTproduct,inventory,_CHANGE_TYPEASchange_type,_CHANGE_TIMESTAMPASchange_timeFROMCHANGES(TABLEmydataset.Produce,NULL,TIMESTAMP_SUB(CURRENT_TIMESTAMP(),INTERVAL601SECOND))ORDERBYchange_time,product;

The output is similar to the following:

+---------+-----------+-------------+---------------------+| product | inventory | change_type |     change_time     |+---------+-----------+-------------+---------------------+| bananas |        20 | INSERT      | 2024-01-09 17:13:58 || carrots |        30 | INSERT      | 2024-01-09 17:13:58 || bananas |        20 | DELETE      | 2024-01-09 17:14:30 || carrots |        30 | DELETE      | 2024-01-09 17:15:24 || carrots |        20 | UPDATE      | 2024-01-09 17:15:24 |+---------+-----------+-------------+---------------------+

Enabling change history for an existing table

To set theenable_change_history optiontoTRUE for an existing table, use theALTER TABLE SET OPTIONS DDL statement.The following example updates the change history option formy_table toTRUE:

ALTERTABLE`my_dataset.my_table`SETOPTIONS(enable_change_history=TRUE);

DATE_BUCKET

DATE_BUCKET(date_in_bucket,bucket_width)
DATE_BUCKET(date_in_bucket,bucket_width,bucket_origin_date)

Description

Gets the lower bound of the date bucket that contains a date.

Definitions

  • date_in_bucket: ADATE value that you can use to look up a date bucket.
  • bucket_width: AnINTERVAL value that represents the width ofa date bucket. Asingle interval withdate parts is supported.
  • bucket_origin_date: ADATE value that represents a point in time. Allbuckets expand left and right from this point. If this argument isn't set,1950-01-01 is used by default.

Return type

DATE

Examples

In the following example, the origin is omitted and the default origin,1950-01-01 is used. All buckets expand in both directions from the origin,and the size of each bucket is two days. The lower bound of the bucket inwhichmy_date belongs is returned.

WITHsome_datesAS(SELECTDATE'1949-12-29'ASmy_dateUNIONALLSELECTDATE'1949-12-30'UNIONALLSELECTDATE'1949-12-31'UNIONALLSELECTDATE'1950-01-01'UNIONALLSELECTDATE'1950-01-02'UNIONALLSELECTDATE'1950-01-03')SELECTDATE_BUCKET(my_date,INTERVAL2DAY)ASbucket_lower_boundFROMsome_dates;/*--------------------+ | bucket_lower_bound | +--------------------+ | 1949-12-28         | | 1949-12-30         | | 1949-12-30         | | 1950-01-01         | | 1950-01-01         | | 1950-01-03         | +--------------------*/-- Some date buckets that originate from 1950-01-01:-- + Bucket: ...-- + Bucket: [1949-12-28, 1949-12-30)-- + Bucket: [1949-12-30, 1950-01-01)-- + Origin: [1950-01-01]-- + Bucket: [1950-01-01, 1950-01-03)-- + Bucket: [1950-01-03, 1950-01-05)-- + Bucket: ...

In the following example, the origin has been changed to2000-12-24,and all buckets expand in both directions from this point. The size of eachbucket is seven days. The lower bound of the bucket in whichmy_date belongsis returned:

WITHsome_datesAS(SELECTDATE'2000-12-20'ASmy_dateUNIONALLSELECTDATE'2000-12-21'UNIONALLSELECTDATE'2000-12-22'UNIONALLSELECTDATE'2000-12-23'UNIONALLSELECTDATE'2000-12-24'UNIONALLSELECTDATE'2000-12-25')SELECTDATE_BUCKET(my_date,INTERVAL7DAY,DATE'2000-12-24')ASbucket_lower_boundFROMsome_dates;/*--------------------+ | bucket_lower_bound | +--------------------+ | 2000-12-17         | | 2000-12-17         | | 2000-12-17         | | 2000-12-17         | | 2000-12-24         | | 2000-12-24         | +--------------------*/-- Some date buckets that originate from 2000-12-24:-- + Bucket: ...-- + Bucket: [2000-12-10, 2000-12-17)-- + Bucket: [2000-12-17, 2000-12-24)-- + Origin: [2000-12-24]-- + Bucket: [2000-12-24, 2000-12-31)-- + Bucket: [2000-12-31, 2000-01-07)-- + Bucket: ...

DATETIME_BUCKET

DATETIME_BUCKET(datetime_in_bucket,bucket_width)
DATETIME_BUCKET(datetime_in_bucket,bucket_width,bucket_origin_datetime)

Description

Gets the lower bound of the datetime bucket that contains a datetime.

Definitions

  • datetime_in_bucket: ADATETIME value that you can use to look up adatetime bucket.
  • bucket_width: AnINTERVAL value that represents the width ofa datetime bucket. Asingle interval withdate and time parts is supported.
  • bucket_origin_datetime: ADATETIME value that represents a point intime. All buckets expand left and right from this point. If this argumentisn't set,1950-01-01 00:00:00 is used by default.

Return type

DATETIME

Examples

In the following example, the origin is omitted and the default origin,1950-01-01 00:00:00 is used. All buckets expand in both directions from theorigin, and the size of each bucket is 12 hours. The lower bound of the bucketin whichmy_datetime belongs is returned:

WITHsome_datetimesAS(SELECTDATETIME'1949-12-30 13:00:00'ASmy_datetimeUNIONALLSELECTDATETIME'1949-12-31 00:00:00'UNIONALLSELECTDATETIME'1949-12-31 13:00:00'UNIONALLSELECTDATETIME'1950-01-01 00:00:00'UNIONALLSELECTDATETIME'1950-01-01 13:00:00'UNIONALLSELECTDATETIME'1950-01-02 00:00:00')SELECTDATETIME_BUCKET(my_datetime,INTERVAL12HOUR)ASbucket_lower_boundFROMsome_datetimes;/*---------------------+ | bucket_lower_bound  | +---------------------+ | 1949-12-30T12:00:00 | | 1949-12-31T00:00:00 | | 1949-12-31T12:00:00 | | 1950-01-01T00:00:00 | | 1950-01-01T12:00:00 | | 1950-01-02T00:00:00 | +---------------------*/-- Some datetime buckets that originate from 1950-01-01 00:00:00:-- + Bucket: ...-- + Bucket: [1949-12-30 00:00:00, 1949-12-30 12:00:00)-- + Bucket: [1949-12-30 12:00:00, 1950-01-01 00:00:00)-- + Origin: [1950-01-01 00:00:00]-- + Bucket: [1950-01-01 00:00:00, 1950-01-01 12:00:00)-- + Bucket: [1950-01-01 12:00:00, 1950-02-00 00:00:00)-- + Bucket: ...

In the following example, the origin has been changed to2000-12-24 12:00:00,and all buckets expand in both directions from this point. The size of eachbucket is seven days. The lower bound of the bucket in whichmy_datetimebelongs is returned:

WITHsome_datetimesAS(SELECTDATETIME'2000-12-20 00:00:00'ASmy_datetimeUNIONALLSELECTDATETIME'2000-12-21 00:00:00'UNIONALLSELECTDATETIME'2000-12-22 00:00:00'UNIONALLSELECTDATETIME'2000-12-23 00:00:00'UNIONALLSELECTDATETIME'2000-12-24 00:00:00'UNIONALLSELECTDATETIME'2000-12-25 00:00:00')SELECTDATETIME_BUCKET(my_datetime,INTERVAL7DAY,DATETIME'2000-12-22 12:00:00')ASbucket_lower_boundFROMsome_datetimes;/*--------------------+ | bucket_lower_bound | +--------------------+ | 2000-12-15T12:00:00 | | 2000-12-15T12:00:00 | | 2000-12-15T12:00:00 | | 2000-12-22T12:00:00 | | 2000-12-22T12:00:00 | | 2000-12-22T12:00:00 | +--------------------*/-- Some datetime buckets that originate from 2000-12-22 12:00:00:-- + Bucket: ...-- + Bucket: [2000-12-08 12:00:00, 2000-12-15 12:00:00)-- + Bucket: [2000-12-15 12:00:00, 2000-12-22 12:00:00)-- + Origin: [2000-12-22 12:00:00]-- + Bucket: [2000-12-22 12:00:00, 2000-12-29 12:00:00)-- + Bucket: [2000-12-29 12:00:00, 2000-01-05 12:00:00)-- + Bucket: ...

GAP_FILL

GAP_FILL(TABLEtime_series_table,time_series_column,bucket_width,[,partitioning_columns=>value][,value_columns=>value][,origin=>value][,ignore_null_values=>{TRUE|FALSE}])
GAP_FILL((time_series_subquery),time_series_column,bucket_width,[,partitioning_columns=>values][,value_columns=>value][,origin=>value][,ignore_null_values=>{TRUE|FALSE}])

Description

Finds and fills gaps in a time series.

Definitions

  • time_series_table: The name of the table that contains thetime series data.
  • time_series_subquery: The subquery that contains the time series data.
  • time_series_column: The name of the column intime_series_table ortime_series_subquery that contains the time points of thetime series data. This column must represent aDATE,DATETIME,orTIMESTAMP type.
  • bucket_width: TheINTERVAL value that represents the selected widthof the time buckets. The interval can represent aDATE,DATETIME, orTIMESTAMP type.
  • partitioning_columns: A named argument with anARRAY<STRING> value.Represents an array of zero or more column names used to partition datainto individual time series (time series identity). This has the same columntype requirements as thePARTITION BY clause.

  • value_columns: A named argument with anARRAY<STRUCT<STRING, STRING>>value. Represents an array of column name and gap-filling method pairs inthe following format:

    [(column_name,gap_filling_method),...]

    • column_name: ASTRING value that represents a valid column fromtime_series_table. A column name can only be used once invalue_columns.

    • gap_filling_method: ASTRING value that can be one of the followinggap-filling methods:

      • null (default): Fill in missing values withNULL values.

      • linear: Fill in missing values usinglinear interpolation. So, when a new value is added, it's based ona linear slope for a specific time bucket. When this method isused,column_name must be a numeric data type.

      • locf: Fill in missing values by carrying the last observed valueforward. So, when a new value is added, it's based onthe previous value.

  • origin: ADATE,DATETIME orTIMESTAMP optional named argument.Represents a point in time from which all time buckets expand ineach direction.

    Iforigin isn't provided, the data type fortime_series_column isassumed, and the corresponding default value is used:

    • DATE '1950-01-01'
    • DATETIME '1950-01-01 00:00:00'
    • TIMESTAMP '1950-01-01 00:00:00'

  • ignore_null_values: A named argument with aBOOL value. Indicateswhether the function ignoresNULL values in the input data when performinggap filling. By default, this value isTRUE.

    • IfTRUE (default),NULL values are skipped during gap filling.

      • null is the gap-filling method for a column: If a value in acolumn isNULL, the output isNULL for that column.

      • locf orlinear is the gap-filling method for a column: Theprevious or next non-NULL value is used. The side effect of thisis that output value columns are neverNULL, except for the edges.

    • IfFALSE,NULL values are included during gap filling.

      • null is the gap-filling method for a column: If a value in acolumn isNULL, the output isNULL for that column.

      • locf is the gap-filling method for a column: If the previousvalue in that column isNULL, the output isNULL for thatcolumn.

      • linear is the gap-filling method for a column: If either ofthe endpoints in that column isNULL, the output isNULL forthat column.

Details

Sometimes the fixed time intervals produced by time bucket functions have gaps,either due to irregular sampling intervals or an event that caused data lossfor some time period. This can cause irregularities in reporting. For example,a plot with irregular intervals might have visible discontinuity. You can usetheGAP_FILL function to employ various gap-filling methods to fill inthose missing data points.

time_series_column andorigin must be of the same data type.

Return type

TABLE

Examples

In the following query, thelocf gap-filling method is applied to gaps:

CREATETEMPTABLEdevice_dataASSELECT*FROMUNNEST(ARRAY<STRUCT<device_idINT64,timeDATETIME,signalINT64,stateSTRING>>[STRUCT(1,DATETIME'2023-11-01 09:34:01',74,'INACTIVE'),STRUCT(2,DATETIME'2023-11-01 09:36:00',77,'ACTIVE'),STRUCT(3,DATETIME'2023-11-01 09:37:00',78,'ACTIVE'),STRUCT(4,DATETIME'2023-11-01 09:38:01',80,'ACTIVE')]);SELECT*FROMGAP_FILL(TABLEdevice_data,ts_column=>'time',bucket_width=>INTERVAL1MINUTE,value_columns=>[('signal','locf')])ORDERBYtime;/*---------------------+--------+ | time                | signal | +---------------------+--------+ | 2023-11-01T09:35:00 | 74     | | 2023-11-01T09:36:00 | 77     | | 2023-11-01T09:37:00 | 78     | | 2023-11-01T09:38:00 | 78     | +---------------------+--------*/

In the following query, thelinear gap-filling method is applied to gaps:

CREATETEMPTABLEdevice_dataASSELECT*FROMUNNEST(ARRAY<STRUCT<device_idINT64,timeDATETIME,signalINT64,stateSTRING>>[STRUCT(1,DATETIME'2023-11-01 09:34:01',74,'INACTIVE'),STRUCT(2,DATETIME'2023-11-01 09:36:00',77,'ACTIVE'),STRUCT(3,DATETIME'2023-11-01 09:37:00',78,'ACTIVE'),STRUCT(4,DATETIME'2023-11-01 09:38:01',80,'ACTIVE')]);SELECT*FROMGAP_FILL(TABLEdevice_data,ts_column=>'time',bucket_width=>INTERVAL1MINUTE,value_columns=>[('signal','linear')])ORDERBYtime;/*---------------------+--------+ | time                | signal | +---------------------+--------+ | 2023-11-01T09:35:00 | 75     | | 2023-11-01T09:36:00 | 77     | | 2023-11-01T09:37:00 | 78     | | 2023-11-01T09:38:00 | 80     | +---------------------+--------*/

In the following query, thenull gap-filling method is applied to gaps:

CREATETEMPTABLEdevice_dataASSELECT*FROMUNNEST(ARRAY<STRUCT<device_idINT64,timeDATETIME,signalINT64,stateSTRING>>[STRUCT(1,DATETIME'2023-11-01 09:34:01',74,'INACTIVE'),STRUCT(2,DATETIME'2023-11-01 09:36:00',77,'ACTIVE'),STRUCT(3,DATETIME'2023-11-01 09:37:00',78,'ACTIVE'),STRUCT(4,DATETIME'2023-11-01 09:38:01',80,'ACTIVE')]);SELECT*FROMGAP_FILL(TABLEdevice_data,ts_column=>'time',bucket_width=>INTERVAL1MINUTE,value_columns=>[('signal','null')])ORDERBYtime;/*---------------------+--------+ | time                | signal | +---------------------+--------+ | 2023-11-01T09:35:00 | NULL   | | 2023-11-01T09:36:00 | 77     | | 2023-11-01T09:37:00 | 78     | | 2023-11-01T09:38:00 | NULL   | +---------------------+--------*/

In the following query,NULL values in the input data are ignored by default:

CREATETEMPTABLEdevice_dataASSELECT*FROMUNNEST(ARRAY<STRUCT<device_idINT64,timeDATETIME,signalINT64,stateSTRING>>[STRUCT(1,DATETIME'2023-11-01 09:34:01',74,'INACTIVE'),STRUCT(2,DATETIME'2023-11-01 09:36:00',77,'ACTIVE'),STRUCT(3,DATETIME'2023-11-01 09:37:00',NULL,'ACTIVE'),STRUCT(4,DATETIME'2023-11-01 09:38:01',80,'ACTIVE')]);SELECT*FROMGAP_FILL(TABLEdevice_data,ts_column=>'time',bucket_width=>INTERVAL1MINUTE,value_columns=>[('signal','linear')])ORDERBYtime;/*---------------------+--------+ | time                | signal | +---------------------+--------+ | 2023-11-01T09:35:00 | 75     | | 2023-11-01T09:36:00 | 77     | | 2023-11-01T09:37:00 | 78     | | 2023-11-01T09:38:00 | 80     | +---------------------+--------*/

In the following query,NULL values in the input data aren't ignored, usingtheignore_null_values argument:

CREATETEMPTABLEdevice_dataASSELECT*FROMUNNEST(ARRAY<STRUCT<device_idINT64,timeDATETIME,signalINT64,stateSTRING>>[STRUCT(1,DATETIME'2023-11-01 09:34:01',74,'INACTIVE'),STRUCT(2,DATETIME'2023-11-01 09:36:00',77,'ACTIVE'),STRUCT(3,DATETIME'2023-11-01 09:37:00',NULL,'ACTIVE'),STRUCT(4,DATETIME'2023-11-01 09:38:01',80,'ACTIVE')]);SELECT*FROMGAP_FILL(TABLEdevice_data,ts_column=>'time',bucket_width=>INTERVAL1MINUTE,value_columns=>[('signal','linear')],ignore_null_values=>FALSE)ORDERBYtime;/*---------------------+--------+ | time                | signal | +---------------------+--------+ | 2023-11-01T09:35:00 | 75     | | 2023-11-01T09:36:00 | 77     | | 2023-11-01T09:37:00 | NULL   | | 2023-11-01T09:38:00 | NULL   | +---------------------+--------*/

In the following query, when thevalue_columns argument isn't passed in,thenull gap-filling method is used on all columns:

CREATETEMPTABLEdevice_dataASSELECT*FROMUNNEST(ARRAY<STRUCT<device_idINT64,timeDATETIME,signalINT64,stateSTRING>>[STRUCT(1,DATETIME'2023-11-01 09:34:01',74,'INACTIVE'),STRUCT(2,DATETIME'2023-11-01 09:36:00',77,'ACTIVE'),STRUCT(3,DATETIME'2023-11-01 09:37:00',79,'ACTIVE'),STRUCT(4,DATETIME'2023-11-01 09:38:01',80,'ACTIVE')]);SELECT*FROMGAP_FILL(TABLEdevice_data,ts_column=>'time',bucket_width=>INTERVAL1MINUTE)ORDERBYtime;/*---------------------+-----------+--------+----------+ | time                | device_id | signal | state    | +---------------------+-----------+--------+----------+ | 2023-11-01T09:35:00 | NULL      | NULL   | NULL     | | 2023-11-01T09:36:00 | 2         | 77     | ACTIVE   | | 2023-11-01T09:37:00 | 3         | 79     | ACTIVE   | | 2023-11-01T09:38:00 | NULL      | NULL   | NULL     | +---------------------+-----------+--------+----------*/

In the following query, rows (buckets) are added for gaps that are found:

CREATETEMPTABLEdevice_dataASSELECT*FROMUNNEST(ARRAY<STRUCT<device_idINT64,timeDATETIME,signalINT64,stateSTRING>>[STRUCT(1,DATETIME'2023-11-01 09:35:39',74,'INACTIVE'),STRUCT(2,DATETIME'2023-11-01 09:37:39',77,'ACTIVE'),STRUCT(3,DATETIME'2023-11-01 09:38:00',77,'ACTIVE'),STRUCT(4,DATETIME'2023-11-01 09:40:00',80,'ACTIVE')]);SELECT*FROMGAP_FILL(TABLEdevice_data,ts_column=>'time',bucket_width=>INTERVAL1MINUTE,value_columns=>[('signal','locf')])ORDERBYtime;/*---------------------+--------+ | time                | signal | +---------------------+--------+ | 2023-11-01T09:36:00 | 74     | | 2023-11-01T09:37:00 | 74     | | 2023-11-01T09:38:00 | 74     | | 2023-11-01T09:39:00 | 77     | | 2023-11-01T09:40:00 | 77     | +---------------------+--------*/

In the following query, data is condensed when it fits in the same bucket andhas the same values:

CREATETEMPTABLEdevice_dataASSELECT*FROMUNNEST(ARRAY<STRUCT<device_idINT64,timeDATETIME,signalINT64,stateSTRING>>[STRUCT(1,DATETIME'2023-11-01 09:35:39',74,'INACTIVE'),STRUCT(2,DATETIME'2023-11-01 09:36:60',77,'ACTIVE'),STRUCT(3,DATETIME'2023-11-01 09:37:00',77,'ACTIVE'),STRUCT(4,DATETIME'2023-11-01 09:37:20',80,'ACTIVE')]);SELECT*FROMGAP_FILL(TABLEdevice_data,ts_column=>'time',bucket_width=>INTERVAL1MINUTE,value_columns=>[('signal','locf')])ORDERBYtime;/*---------------------+--------+ | time                | signal | +---------------------+--------+ | 2023-11-01T09:36:00 | 74     | | 2023-11-01T09:37:00 | 77     | +---------------------+--------*/

In the following query, gap filling is applied to partitions:

CREATETEMPTABLEdevice_dataASSELECT*FROMUNNEST(ARRAY<STRUCT<device_idINT64,timeDATETIME,signalINT64,stateSTRING>>[STRUCT(2,DATETIME'2023-11-01 09:35:07',87,'ACTIVE'),STRUCT(1,DATETIME'2023-11-01 09:35:26',82,'ACTIVE'),STRUCT(3,DATETIME'2023-11-01 09:35:39',74,'INACTIVE'),STRUCT(2,DATETIME'2023-11-01 09:36:07',88,'ACTIVE'),STRUCT(1,DATETIME'2023-11-01 09:36:26',82,'ACTIVE'),STRUCT(2,DATETIME'2023-11-01 09:37:07',88,'ACTIVE'),STRUCT(1,DATETIME'2023-11-01 09:37:28',80,'ACTIVE'),STRUCT(3,DATETIME'2023-11-01 09:37:39',77,'ACTIVE'),STRUCT(2,DATETIME'2023-11-01 09:38:07',86,'ACTIVE'),STRUCT(1,DATETIME'2023-11-01 09:38:26',81,'ACTIVE'),STRUCT(3,DATETIME'2023-11-01 09:38:39',77,'ACTIVE')]);SELECT*FROMGAP_FILL(TABLEdevice_data,ts_column=>'time',bucket_width=>INTERVAL1MINUTE,partitioning_columns=>['device_id'],value_columns=>[('signal','locf')])ORDERBYdevice_id;/*---------------------+-----------+--------+ | time                | device_id | signal | +---------------------+-----------+--------+ | 2023-11-01T09:36:00 | 1         | 82     | | 2023-11-01T09:37:00 | 1         | 82     | | 2023-11-01T09:38:00 | 1         | 80     | | 2023-11-01T09:36:00 | 2         | 87     | | 2023-11-01T09:37:00 | 2         | 88     | | 2023-11-01T09:38:00 | 2         | 88     | | 2023-11-01T09:36:00 | 3         | 74     | | 2023-11-01T09:37:00 | 3         | 74     | | 2023-11-01T09:38:00 | 3         | 77     | +---------------------+-----------+--------*/

In the following query, gap filling is applied to multiple columns, and eachcolumn uses a different gap-filling method:

CREATETEMPTABLEdevice_dataASSELECT*FROMUNNEST(ARRAY<STRUCT<device_idINT64,timeDATETIME,signalINT64,stateSTRING>>[STRUCT(1,DATETIME'2023-11-01 09:34:01',74,'ACTIVE'),STRUCT(2,DATETIME'2023-11-01 09:36:00',77,'INACTIVE'),STRUCT(3,DATETIME'2023-11-01 09:38:00',78,'ACTIVE'),STRUCT(4,DATETIME'2023-11-01 09:39:01',80,'ACTIVE')]);SELECT*FROMGAP_FILL(TABLEdevice_data,ts_column=>'time',bucket_width=>INTERVAL1MINUTE,value_columns=>[('signal','linear'),('state','locf')])ORDERBYtime;/*---------------------+--------+----------+ | time                | signal | state    | +---------------------+--------+----------+ | 2023-11-01T09:35:00 | 75     | ACTIVE   | | 2023-11-01T09:36:00 | 77     | INACTIVE | | 2023-11-01T09:37:00 | 78     | INACTIVE | | 2023-11-01T09:38:00 | 78     | ACTIVE   | | 2023-11-01T09:39:00 | 80     | ACTIVE   | +---------------------+--------+----------*/

In the following query, the point of origin is changed in the gap-fillingresults to a custom origin, using theorigin argument:

CREATETEMPTABLEdevice_dataASSELECT*FROMUNNEST(ARRAY<STRUCT<device_idINT64,timeDATETIME,signalINT64,stateSTRING>>[STRUCT(1,DATETIME'2023-11-01 09:34:01',74,'ACTIVE'),STRUCT(2,DATETIME'2023-11-01 09:36:00',77,'INACTIVE'),STRUCT(3,DATETIME'2023-11-01 09:38:00',78,'ACTIVE'),STRUCT(4,DATETIME'2023-11-01 09:39:01',80,'ACTIVE')]);SELECT*FROMGAP_FILL(TABLEdevice_data,ts_column=>'time',bucket_width=>INTERVAL1MINUTE,value_columns=>[('signal','null')],origin=>DATETIME'2023-11-01 09:30:01')ORDERBYtime;/*---------------------+--------+ | time                | signal | +---------------------+--------+ | 2023-11-01T09:34:01 | 74     | | 2023-11-01T09:35:01 | NULL   | | 2023-11-01T09:36:01 | NULL   | | 2023-11-01T09:37:01 | NULL   | | 2023-11-01T09:38:01 | NULL   | | 2023-11-01T09:39:01 | 80     | +---------------------+--------*/

In the following query, a subquery is passed into the function instead of atable:

SELECT*FROMGAP_FILL((SELECT*FROMUNNEST(ARRAY<STRUCT<device_idINT64,timeDATETIME,signalINT64,stateSTRING>>[STRUCT(1,DATETIME'2023-11-01 09:34:01',74,'INACTIVE'),STRUCT(2,DATETIME'2023-11-01 09:36:00',77,'ACTIVE'),STRUCT(3,DATETIME'2023-11-01 09:37:00',78,'ACTIVE'),STRUCT(4,DATETIME'2023-11-01 09:38:01',80,'ACTIVE')])),ts_column=>'time',bucket_width=>INTERVAL1MINUTE,value_columns=>[('signal','linear')])ORDERBYtime;/*---------------------+--------+ | time                | signal | +---------------------+--------+ | 2023-11-01T09:35:00 | 75     | | 2023-11-01T09:36:00 | 77     | | 2023-11-01T09:37:00 | 78     | | 2023-11-01T09:38:00 | 80     | +---------------------+--------*/

TIMESTAMP_BUCKET

TIMESTAMP_BUCKET(timestamp_in_bucket,bucket_width)
TIMESTAMP_BUCKET(timestamp_in_bucket,bucket_width,bucket_origin_timestamp)

Description

Gets the lower bound of the timestamp bucket that contains a timestamp.

Definitions

  • timestamp_in_bucket: ATIMESTAMP value that you can use to look up atimestamp bucket.
  • bucket_width: AnINTERVAL value that represents the width ofa timestamp bucket. Asingle interval withdate and time parts is supported.
  • bucket_origin_timestamp: ATIMESTAMP value that represents a point intime. All buckets expand left and right from this point. If this argumentisn't set,1950-01-01 00:00:00 is used by default.

Return type

TIMESTAMP

Examples

In the following example, the origin is omitted and the default origin,1950-01-01 00:00:00 is used. All buckets expand in both directions from theorigin, and the size of each bucket is 12 hours. The default time zone,UTC, is included in the results. The lower bound of thebucket in whichmy_timestamp belongs is returned:

WITHsome_timestampsAS(SELECTTIMESTAMP'1949-12-30 13:00:00.00'ASmy_timestampUNIONALLSELECTTIMESTAMP'1949-12-31 00:00:00.00'UNIONALLSELECTTIMESTAMP'1949-12-31 13:00:00.00'UNIONALLSELECTTIMESTAMP'1950-01-01 00:00:00.00'UNIONALLSELECTTIMESTAMP'1950-01-01 13:00:00.00'UNIONALLSELECTTIMESTAMP'1950-01-02 00:00:00.00')SELECTTIMESTAMP_BUCKET(my_timestamp,INTERVAL12HOUR)ASbucket_lower_boundFROMsome_timestamps;-- Display of results may differ, depending upon the environment and-- time zone where this query was executed./*------------------------+ | bucket_lower_bound      | +-------------------------+ | 1949-12-30 12:00:00 UTC | | 1949-12-31 00:00:00 UTC | | 1949-12-31 12:00:00 UTC | | 1950-01-01 00:00:00 UTC | | 1950-01-01 12:00:00 UTC | | 1950-01-02 00:00:00 UTC | +-------------------------*/-- Some timestamp buckets that originate from 1950-01-01 00:00:00:-- + Bucket: ...-- + Bucket: [1949-12-30 00:00:00.00 UTC, 1949-12-30 12:00:00.00 UTC)-- + Bucket: [1949-12-30 12:00:00.00 UTC, 1950-01-01 00:00:00.00 UTC)-- + Origin: [1950-01-01 00:00:00.00 UTC]-- + Bucket: [1950-01-01 00:00:00.00 UTC, 1950-01-01 12:00:00.00 UTC)-- + Bucket: [1950-01-01 12:00:00.00 UTC, 1950-02-00 00:00:00.00 UTC)-- + Bucket: ...

In the following example, the origin has been changed to2000-12-24 12:00:00,and all buckets expand in both directions from this point. The size of eachbucket is seven days. The default time zone, UTC, is includedin the results. The lower bound of the bucket in whichmy_timestampbelongs is returned:

WITHsome_timestampsAS(SELECTTIMESTAMP'2000-12-20 00:00:00.00'ASmy_timestampUNIONALLSELECTTIMESTAMP'2000-12-21 00:00:00.00'UNIONALLSELECTTIMESTAMP'2000-12-22 00:00:00.00'UNIONALLSELECTTIMESTAMP'2000-12-23 00:00:00.00'UNIONALLSELECTTIMESTAMP'2000-12-24 00:00:00.00'UNIONALLSELECTTIMESTAMP'2000-12-25 00:00:00.00')SELECTTIMESTAMP_BUCKET(my_timestamp,INTERVAL7DAY,TIMESTAMP'2000-12-22 12:00:00.00')ASbucket_lower_boundFROMsome_timestamps;-- Display of results may differ, depending upon the environment and-- time zone where this query was executed./*------------------------+ | bucket_lower_bound      | +-------------------------+ | 2000-12-15 12:00:00 UTC | | 2000-12-15 12:00:00 UTC | | 2000-12-15 12:00:00 UTC | | 2000-12-22 12:00:00 UTC | | 2000-12-22 12:00:00 UTC | | 2000-12-22 12:00:00 UTC | +-------------------------*/-- Some timestamp buckets that originate from 2000-12-22 12:00:00:-- + Bucket: ...-- + Bucket: [2000-12-08 12:00:00.00 UTC, 2000-12-15 12:00:00.00 UTC)-- + Bucket: [2000-12-15 12:00:00.00 UTC, 2000-12-22 12:00:00.00 UTC)-- + Origin: [2000-12-22 12:00:00.00 UTC]-- + Bucket: [2000-12-22 12:00:00.00 UTC, 2000-12-29 12:00:00.00 UTC)-- + Bucket: [2000-12-29 12:00:00.00 UTC, 2000-01-05 12:00:00.00 UTC)-- + Bucket: ...

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.