This page provides an overview of all GoogleSQL for BigQuerydata types, including information about their valuedomains. Forinformation on data type literals and constructors, seeLexical Structure and Syntax.

Data type list

Data type properties

When storing and querying data, it's helpful to keep the following data typeproperties in mind:

Nullable data types

For nullable data types,NULL is a valid value. Currently, all existingdata types are nullable. Conditions apply forarrays.

Orderable data types

Expressions of orderable data types can be used in anORDER BY clause.Applies to all data types except for:

• ARRAY
• STRUCT
• GEOGRAPHY
• JSON

OrderingNULLs

In the context of theORDER BY clause,NULLs are the minimumpossible value; that is,NULLs appear first inASC sorts and last inDESC sorts.

NULL values can be specified as the first or last values for a columnirrespective ofASC orDESC by using theNULLS FIRST orNULLS LASTmodifiers respectively.

To learn more about usingASC,DESC,NULLS FIRST andNULLS LAST, seetheORDER BY clause.

Ordering floating points

Floating point values are sorted in this order, from least to greatest:

1. NULL
2. NaN — AllNaN values are considered equal when sorting.
3. -inf
4. Negative numbers
5. 0 or -0 — All zero values are considered equal when sorting.
6. Positive numbers
7. +inf

Groupable data types

Groupable data types can generally appear in an expression followingGROUP BY,DISTINCT, andPARTITION BY. All data types are supported except for:

• GEOGRAPHY
• JSON

Grouping with floating point types

Groupable floating point types can appear in an expression followingGROUP BYandDISTINCT.PARTITION BY expressions can'tincludefloating point types.

Special floating point values are grouped in the following way, includingboth grouping done by aGROUP BY clause and grouping done by theDISTINCT keyword:

• NULL
• NaN — AllNaN values are considered equal when grouping.
• -inf
• 0 or -0 — All zero values are considered equal when grouping.
• +inf

Grouping with arrays

AnARRAY type is groupable if its element type isgroupable. AnARRAY typeis only groupable in aGROUP BY clause or in aSELECT DISTINCT clause.

Two arrays are in the same group if and only if one of the following statementsis true:

• The two arrays are bothNULL.
• The two arrays have the same number of elements and all correspondingelements are in the same groups.

Grouping with structs

ASTRUCT type is groupable if its field types aregroupable. ASTRUCT typeis only groupable in aGROUP BY clause or in aSELECT DISTINCT clause.

Two structs are in the same group if and only if one of the following statementsis true:

• The two structs are bothNULL.
• All corresponding field values between the structs are in the same groups.

Comparable data types

Values of the same comparable data type can be compared to each other.All data types are supported except for:

• GEOGRAPHY
• JSON
• ARRAY

Notes:

• Equality comparisons for structs are supported field by field, infield order. Field names are ignored. Less than and greater than comparisonsaren't supported.
• To compare geography values, useST_Equals.
• When comparing ranges, the lower bounds are compared. If the lower bounds areequal, the upper bounds are compared, instead.
• When comparing ranges,NULL values are handled as follows:
  • NULL lower bounds are sorted before non-NULL lower bounds.
  • NULL upper bounds are sorted after non-NULL upper bounds.
  • If two bounds that are being compared areNULL, the comparison isTRUE.
  • AnUNBOUNDED bound is treated as aNULL bound.
• All types that support comparisons can be used in aJOIN condition.SeeJOIN Types for an explanation of join conditions.

Collatable data types

Collatable data types support collation, which determines how to sort andcompare strings. These data types support collation:

• String
• String fields in a struct
• String elements in an array

Data type sizes

Use the following table to see the size in logical bytes for each supported datatype.

ANULL value for any data type is calculated as 0 logical bytes.

A repeated column is stored as an array, and the size is calculated based on thecolumn data type and the number of values. For example, an integer column(INT64) that's repeated (ARRAY<INT64>) and contains 4 entries is calculatedas 32 logical bytes (4 entries x 8 logical bytes). The total size of all valuesin a table row can't exceed themaximum row size.

Parameterized data types

Syntax:

DATA_TYPE(param[,...])

You can use parameters to specify constraints for the following data types:

• STRING
• BYTES
• NUMERIC
• BIGNUMERIC

A data type that's declared with parameters is called a parameterized datatype. You can only use parameterized data types with columns and scriptvariables. A column with a parameterized data type is aparameterized columnand a script variable with a parameterized data type is aparameterized scriptvariable. Parameterized type constraints are enforced when writing a value to aparameterized column or when assigning a value to a parameterized scriptvariable.

A data type's parameters aren't propagated in an expression, only the data typeis.

Examples

-- Declare a variable with type parameters.DECLARExSTRING(10);-- This is a valid assignment to x.SETx="hello";-- This assignment to x violates the type parameter constraint and results in an OUT_OF_RANGE error.SETx="this string is too long"

-- Declare variables with type parameters.DECLARExNUMERIC(10)DEFAULT12345;DECLAREyNUMERIC(5,2)DEFAULT123.45;-- The variable x is treated as a NUMERIC value when read, so the result of this query-- is a NUMERIC without type parameters.SELECTx;-- Type parameters aren't propagated within expressions, so variables x and y are treated-- as NUMERIC values when read and the result of this query is a NUMERIC without type parameters.SELECTx+y;

Array type

An array is an ordered list of zero or more elements of non-array values.Elements in an array must share the same type.

Arrays of arrays aren't allowed. Queries that would produce an array ofarrays return an error. Instead, a struct must be inserted between thearrays using theSELECT AS STRUCT construct.

To learn more about the literal representation of an array type,seeArray literals.

To learn more about using arrays in GoogleSQL, seeWork witharrays.

NULLs and the array type

Currently, GoogleSQL for BigQuery has the following rules with respect toNULLs andarrays:

• An array can beNULL.

  For example:

  SELECTCAST(NULLASARRAY<INT64>)ISNULLASarray_is_null;/*---------------+ | array_is_null | +---------------+ | TRUE          | +---------------*/

• GoogleSQL for BigQuery translates aNULL array into an empty array in the queryresult, although inside the query,NULL and empty arrays are two distinctvalues.

  For example:

  WITHItemsAS(SELECT[]ASnumbers,"Empty array in query"ASdescriptionUNIONALLSELECTCAST(NULLASARRAY<INT64>),"NULL array in query")SELECTnumbers,description,numbersISNULLASnumbers_nullFROMItems;/*---------+----------------------+--------------+ | numbers | description          | numbers_null | +---------+----------------------+--------------+ | []      | Empty array in query | false        | | []      | NULL array in query  | true         | +---------+----------------------+--------------*/

  When you write aNULL array to a table, it's converted to anempty array. If you writeItems to a table from the previous query,then each array is written as an empty array:

  SELECTnumbers,description,numbersISNULLASnumbers_nullFROMItems;/*---------+----------------------+--------------+ | numbers | description          | numbers_null | +---------+----------------------+--------------+ | []      | Empty array in query | false        | | []      | NULL array in query  | false        | +---------+----------------------+--------------*/

• GoogleSQL for BigQuery raises an error if the query result has an array whichcontainsNULL elements, although such an array can be used inside the query.

  For example, this works:

  SELECTFORMAT("%T",[1,NULL,3])asnumbers;/*--------------+ | numbers      | +--------------+ | [1, NULL, 3] | +--------------*/

  But this raises an error:

  -- errorSELECT[1,NULL,3]asnumbers;

Declaring an array type

ARRAY<T>

Array types are declared using the angle brackets (< and>). The typeof the elements of an array can be arbitrarily complex with the exception thatan array can't directly contain another array.

Examples

Constructing an array

You can construct an array using array literals or array functions.

Using array literals

You can build an array literal in GoogleSQL using brackets ([ and]). Each element in an array is separated by a comma.

SELECT[1,2,3]ASnumbers;SELECT["apple","pear","orange"]ASfruit;SELECT[true,false,true]ASbooleans;

You can also create arrays from any expressions that have compatible types. Forexample:

SELECT[a,b,c]FROM(SELECT5ASa,37ASb,406ASc);SELECT[a,b,c]FROM(SELECTCAST(5ASINT64)ASa,CAST(37ASFLOAT64)ASb,406ASc);

Notice that the second example contains three expressions: one that returns anINT64, one that returns aFLOAT64, and one thatdeclares a literal. This expression works because all three expressions shareFLOAT64 as a supertype.

To declare a specific data type for an array, use anglebrackets (< and>). For example:

SELECTARRAY<FLOAT64>[1,2,3]ASfloats;

Arrays of most data types, such asINT64 orSTRING, don't requirethat you declare them first.

SELECT[1,2,3]ASnumbers;

You can write an empty array of a specific type usingARRAY<type>[]. You canalso write an untyped empty array using[], in which case GoogleSQLattempts to infer the array type from the surrounding context. IfGoogleSQL can't infer a type, the default typeARRAY<INT64> is used.

Using generated values

You can also construct anARRAY with generated values.

Generating arrays of integers

GENERATE_ARRAYgenerates an array of values from a starting and ending value and a step value.For example, the following query generates an array that contains all of the oddintegers from 11 to 33, inclusive:

SELECTGENERATE_ARRAY(11,33,2)ASodds;/*--------------------------------------------------+ | odds                                             | +--------------------------------------------------+ | [11, 13, 15, 17, 19, 21, 23, 25, 27, 29, 31, 33] | +--------------------------------------------------*/

You can also generate an array of values in descending order by giving anegative step value:

SELECTGENERATE_ARRAY(21,14,-1)AScountdown;/*----------------------------------+ | countdown                        | +----------------------------------+ | [21, 20, 19, 18, 17, 16, 15, 14] | +----------------------------------*/

Generating arrays of dates

GENERATE_DATE_ARRAYgenerates an array ofDATEs from a starting and endingDATE and a stepINTERVAL.

You can generate a set ofDATE values usingGENERATE_DATE_ARRAY. Forexample, this query returns the currentDATE and the followingDATEs at 1WEEK intervals up to and including a laterDATE:

SELECTGENERATE_DATE_ARRAY('2017-11-21','2017-12-31',INTERVAL1WEEK)ASdate_array;/*--------------------------------------------------------------------------+ | date_array                                                               | +--------------------------------------------------------------------------+ | [2017-11-21, 2017-11-28, 2017-12-05, 2017-12-12, 2017-12-19, 2017-12-26] | +--------------------------------------------------------------------------*/

Boolean type

BOOLEAN is an alias forBOOL.

Boolean values are sorted in this order, from least to greatest:

1. NULL
2. FALSE
3. TRUE

Bytes type

String and bytes are separate types that can't be used interchangeably.Most functions on strings are also defined on bytes. The bytes versionoperates on raw bytes rather than Unicode characters. Casts between string andbytes enforce that the bytes are encoded using UTF-8.

You can convert a base64-encodedSTRING expression into theBYTES formatusing theFROM_BASE64 function.You can also convert a sequence ofBYTES into a base64-encodedSTRINGexpression using theTO_BASE64 function.

To learn more about the literal representation of a bytes type,seeBytes literals.

Parameterized bytes type

SeeParameterized Data Types for more information onparameterized types and where they can be used.

Date type

The date type represents a Gregorian calendar date, independent of time zone. Adate value doesn't represent a specific 24-hour time period. Rather, a givendate value represents a different 24-hour period when interpreted in differenttime zones, and may represent a shorter or longer day during daylight savingtime (DST) transitions.To represent an absolute point in time,use atimestamp.

Canonical format

YYYY-[M]M-[D]D

• YYYY: Four-digit year.
• [M]M: One or two digit month.
• [D]D: One or two digit day.

To learn more about the literal representation of a date type,seeDate literals.

Datetime type

A datetime value represents a Gregorian date and a time,as they might be displayed on a watch, independent of time zone.It includes the year, month, day, hour, minute, second,and subsecond.To represent an absolute point in time,use atimestamp.

Canonical format

civil_date_part[time_part]civil_date_part:YYYY-[M]M-[D]Dtime_part:{|T|t}[H]H:[M]M:[S]S[.F]

• YYYY: Four-digit year.
• [M]M: One or two digit month.
• [D]D: One or two digit day.
• { |T|t}: A space or aT ort separator. TheT andtseparators are flags for time.
• [H]H: One or two digit hour (valid values from 00 to 23).
• [M]M: One or two digit minutes (valid values from 00 to 59).
• [S]S: One or two digit seconds (valid values from 00 to 60).
• [.F]: Up to six fractional digits (microsecondprecision).

To learn more about the literal representation of a datetime type,seeDatetime literals.

Geography type

The geography type is based on theOGC SimpleFeatures specification (SFS),and can contain the following objects:

POINT(x_coordinatey_coordinate)

POINT(32210)

POINTEMPTY

LINESTRING(point[,...])

LINESTRING(11,21,3.12.88,3-3)

LINESTRINGEMPTY

POLYGON(interior_ring[,...])interior_ring:(point[,...])

POLYGON((00,22,20,00),(22,34,24,22))

POLYGONEMPTY

MULTIPOINT(point[,...])

MULTIPOINT(032,1239,4867)

MULTIPOINTEMPTY

MULTILINESTRING((linestring)[,...])

MULTILINESTRING((22,34),(56,77))

MULTILINESTRINGEMPTY

MULTIPOLYGON((polygon)[,...])

MULTIPOLYGON(((0-1,10,11,0-1)),((00,22,30,00),(22,34,24,19)))

MULTIPOLYGONEMPTY

GEOMETRYCOLLECTION(geography_object[,...])

GEOMETRYCOLLECTION(MULTIPOINT(-12,012),LINESTRING(-24,06))

GEOMETRYCOLLECTIONEMPTY

The points, linestrings and polygons of a geography value form a simplearrangement on theWGS84 reference ellipsoid.A simple arrangement is one where no point on the WGS84 surface is containedby multiple elements of the collection. If self intersections exist, theyare automatically removed.

The geography that contains no points, linestrings or polygons is called anempty geography. An empty geography isn't associated with a particulargeometry shape. For example, the following query produces the same results:

SELECTST_GEOGFROMTEXT('POINT EMPTY')ASa,ST_GEOGFROMTEXT('GEOMETRYCOLLECTION EMPTY')ASb/*--------------------------+--------------------------+ | a                        | b                        | +--------------------------+--------------------------+ | GEOMETRYCOLLECTION EMPTY | GEOMETRYCOLLECTION EMPTY | +--------------------------+--------------------------*/

The structure of compound geometry objects isn't preserved if asimpler type can be produced. For example, in columnb,GEOMETRYCOLLECTION with(POINT(1 1) andPOINT(2 2) is converted into thesimplest possible geometry,MULTIPOINT(1 1, 2 2).

SELECTST_GEOGFROMTEXT('MULTIPOINT(1 1, 2 2)')ASa,ST_GEOGFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1), POINT(2 2))')ASb/*----------------------+----------------------+ | a                    | b                    | +----------------------+----------------------+ | MULTIPOINT(1 1, 2 2) | MULTIPOINT(1 1, 2 2) | +----------------------+----------------------*/

A geography is the result of, or an argument to, aGeography Function.

Interval type

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.

AnINTERVAL object represents duration or amount of time, without referringto any specific point in time.

Canonical format

[sign]Y-M[sign]D[sign]H:M:S[.F]

• sign:+ or-
• Y: Year
• M: Month
• D: Day
• H: Hour
• M: Minute
• S: Second
• [.F]: Up to six fractional digits (microsecondprecision)

To learn more about the literal representation of an interval type,seeInterval literals.

Constructing an interval

You can construct an interval with an interval literal that supportsasingle datetime part or adatetime part range.

Construct an interval with a single datetime part

INTERVALint64_expressiondatetime_part

You can construct anINTERVAL object with anINT64 expression and oneinterval-supported datetime part. For example:

-- 1 year, 0 months, 0 days, 0 hours, 0 minutes, and 0 seconds (1-0 0 0:0:0)INTERVAL1YEARINTERVAL4QUARTERINTERVAL12MONTH-- 0 years, 3 months, 0 days, 0 hours, 0 minutes, and 0 seconds (0-3 0 0:0:0)INTERVAL1QUARTERINTERVAL3MONTH-- 0 years, 0 months, 42 days, 0 hours, 0 minutes, and 0 seconds (0-0 42 0:0:0)INTERVAL6WEEKINTERVAL42DAY-- 0 years, 0 months, 0 days, 25 hours, 0 minutes, and 0 seconds (0-0 0 25:0:0)INTERVAL25HOURINTERVAL1500MINUTEINTERVAL90000SECOND-- 0 years, 0 months, 0 days, 1 hours, 30 minutes, and 0 seconds (0-0 0 1:30:0)INTERVAL90MINUTE-- 0 years, 0 months, 0 days, 0 hours, 1 minutes, and 30 seconds (0-0 0 0:1:30)INTERVAL90SECOND-- 0 years, 0 months, -5 days, 0 hours, 0 minutes, and 0 seconds (0-0 -5 0:0:0)INTERVAL-5DAY

For additional examples, seeInterval literals.

Construct an interval with a datetime part range

INTERVALdatetime_parts_stringstarting_datetime_partTOending_datetime_part

You can construct anINTERVAL object with aSTRING that contains thedatetime parts that you want to include, a starting datetime part, and an endingdatetime part. The resultingINTERVAL object only includes datetime parts inthe specified range.

You can use one of the following formats with theinterval-supported datetime parts:

For example:

-- 0 years, 8 months, 20 days, 17 hours, 0 minutes, and 0 seconds (0-8 20 17:0:0)INTERVAL'8 20 17'MONTHTOHOUR-- 0 years, 8 months, -20 days, 17 hours, 0 minutes, and 0 seconds (0-8 -20 17:0:0)INTERVAL'8 -20 17'MONTHTOHOUR

For additional examples, seeInterval literals.

Interval-supported date and time parts

You can use the following date parts to construct an interval:

• YEAR: Number of years,Y.
• QUARTER: Number of quarters; each quarter is converted to3 months,M.
• MONTH: Number of months,M. Each12 months is converted to1 year.
• WEEK: Number of weeks; Each week is converted to7 days,D.
• DAY: Number of days,D.

You can use the following time parts to construct an interval:

• HOUR: Number of hours,H.
• MINUTE: Number of minutes,M. Each60 minutes is converted to1 hour.
• SECOND: Number of seconds,S. Each60 seconds is converted to1 minute. Can include up to six fractional digits (microsecondprecision).
• MILLISECOND: Number of milliseconds.
• MICROSECOND: Number of microseconds.

JSON type

Expect these canonicalization behaviors when creating a value of JSON type:

• Booleans, strings, and nulls are preserved exactly.
• Whitespace characters aren't preserved.
• A JSON value can store integers in the range of-9,223,372,036,854,775,808 (minimum signed 64-bit integer) to18,446,744,073,709,551,615 (maximum unsigned 64-bit integer) andfloating point numbers within a domain ofFLOAT64.
• The order of elements in an array is preserved exactly.
• The order of the members of an object isn't guaranteed or preserved.
• If an object has duplicate keys, the first key that's found is preserved.
• Up to 500 levels can be nested.
• The format of the original string representation of a JSON number may not bepreserved.

To learn more about the literal representation of a JSON type,seeJSON literals.

Numeric types

Numeric types include the following types:

• INT64with aliasINT,SMALLINT,INTEGER,BIGINT,TINYINT,BYTEINT

• NUMERIC with aliasDECIMAL

• BIGNUMERIC with aliasBIGDECIMAL

• FLOAT64

Integer type

Integers are numeric values that don't have fractional components.

INT,SMALLINT,INTEGER,BIGINT,TINYINT, andBYTEINT are aliasesforINT64.

To learn more about the literal representation of an integer type,seeInteger literals.

Decimal types

Decimal type values are numeric values with fixed decimal precision and scale.Precision is the number of digits that the number contains. Scale ishow many of these digits appear after the decimal point.

This type can represent decimal fractions exactly, and is suitable for financialcalculations.

DECIMAL is an alias forNUMERIC.

BIGDECIMAL is an alias forBIGNUMERIC.

To learn more about the literal representation of aNUMERIC type,seeNUMERIC literals.

To learn more about the literal representation of aBIGNUMERIC type,seeBIGNUMERIC literals.

To learn more about how BigQuery rounds values stored as aDECIMALtype, seerounding mode.

Parameterized decimal type

If a value has more thanS decimal digits, the value is rounded toS decimal digits. For example, inserting the value1.125 into aNUMERIC(5, 2) column rounds1.125 half-up to1.13.

If a value has more thanP digits, throws anOUT_OF_RANGE error.For example, inserting1111 into aNUMERIC(5, 2) column returns anOUT_OF_RANGE error since1111 is larger than999.99, the maximum allowedvalue in aNUMERIC(5, 2) column.

SeeParameterized Data Types for more information onparameterized types and where they can be used.

To learn more about the literal representation of a floating point type,seeFloating point literals.

Floating point semantics

When working with floating point numbers, there are special non-numeric valuesthat need to be considered:NaN and+/-inf

Arithmetic operators provide standard IEEE-754 behavior for all finite inputvalues that produce finite output and for all operations for which at least oneinput is non-finite. You can perform arithmetic operationswith signed zeros but you can't store a negative zero,-0.0, in a table.

Function calls and operators return an overflow error if the input is finitebut the output would be non-finite. If the input contains non-finite values, theoutput can be non-finite. In general functions don't introduceNaNs or+/-inf. However, specific functions likeIEEE_DIVIDE can return non-finitevalues on finite input. All such cases are noted explicitly inMathematical functions.

Floating point values are approximations.

• The binary format used to represent floating point values can only representa subset of the numbers between the most positive number and mostnegative number in the value range. This enables efficient handling of amuch larger range than would be possible otherwise.Numbers that aren't exactly representable are approximated by utilizing aclose value instead. For example,0.1 can't be represented as an integerscaled by a power of2. When this value is displayed as a string, it'srounded to a limited number of digits, and the value approximating0.1might appear as"0.1", hiding the fact that the value isn't precise.In other situations, the approximation can be visible.
• Summation of floating point values might produce surprising results becauseoflimited precision. For example,(1e30 + 1) - 1e30 = 0, while(1e30 - 1e30) + 1 = 1.0. This isbecause the floating point value doesn't have enough precision torepresent(1e30 + 1), and the result is rounded to1e30.This example also shows that the result of theSUM aggregate function offloating points values depends on the order in which the values areaccumulated. In general, this order isn't deterministic and therefore theresult isn't deterministic. Thus, the resultingSUM offloating point values might not be deterministic and two executions of thesame query on the same tables might produce different results.
• If the above points are concerning, use adecimal type instead.

Mathematical function examples

Comparison operators provide standard IEEE-754 behavior for floating pointinput.

Comparison operator examples

For more information on how these values are ordered and grouped so theycan be compared,seeOrdering floating point values.

Range type

Declare a range type

A range type can be declared as follows:

Construct a range

You can construct a range with theRANGE constructoror arange literal.

Construct a range with a constructor

You can construct a range with theRANGE constructor. To learn more,seeRANGE.

Construct a range with a literal

You can construct a range with a range literal. The canonical format for arange literal has the following parts:

RANGE<T>'[lower_bound, upper_bound)'

• T: The type of range. This can beDATE,DATETIME, orTIMESTAMP.
• lower_bound: The range starts from this value. This can be adate,datetime, ortimestamp literal. If this value isUNBOUNDED orNULL, the range doesn't include a lower bound.
• upper_bound: The range ends before this value. This can be adate,datetime, ortimestamp literal. If this value isUNBOUNDED orNULL, the range doesn't include an upper bound.

T,lower_bound, andupper_bound must be of the same data type.

To learn more about the literal representation of a range type,seeRange literals.

Additional details

The range type doesn't support arithmetic operators.

String type

Input string values must be UTF-8 encoded and output string values will be UTF-8encoded. Alternate encodings like CESU-8 and Modified UTF-8 aren't treated asvalid UTF-8.

All functions and operators that act on string values operate on Unicodecharacters rather than bytes. For example, functions likeSUBSTR andLENGTHapplied to string input count the number of characters, not bytes.

Each Unicode character has a numeric value called a code point assigned to it.Lower code points are assigned to lower characters. When characters arecompared, the code points determine which characters are less than or greaterthan other characters.

Most functions on strings are also defined on bytes. The bytes versionoperates on raw bytes rather than Unicode characters. Strings and bytes areseparate types that can't be used interchangeably. There is no implicit castingin either direction. Explicit casting between string and bytes doesUTF-8 encoding and decoding. Casting bytes to string returns an error if thebytes aren't valid UTF-8.

To learn more about the literal representation of a string type,seeString literals.

Parameterized string type

SeeParameterized Data Types for more information onparameterized types and where they can be used.

Struct type

To learn more about the literal representation of a struct type,seeStruct literals.

Declaring a struct type

STRUCT<T>

Struct types are declared using the angle brackets (< and>). The type ofthe elements of a struct can be arbitrarily complex.

Examples

Constructing a struct

Tuple syntax

(expr1,expr2[,...])

The output type is an anonymous struct type with anonymous fields with typesmatching the types of the input expressions. There must be at least twoexpressions specified. Otherwise this syntax is indistinguishable from anexpression wrapped with parentheses.

Examples

This syntax can also be used with struct comparison for comparison expressionsusing multi-part keys, e.g., in aWHERE clause:

WHERE(Key1,Key2)IN((12,34),(56,78))

Typeless struct syntax

STRUCT(expr1[ASfield_name][,...])

Duplicate field names are allowed. Fields without names are considered anonymousfields and can't be referenced by name. struct values can beNULL, or canhaveNULL field values.

Examples

Typed struct syntax

STRUCT<[field_name]field_type,...>(expr1[,...])

Typed syntax allows constructing structs with an explicit struct data type. Theoutput type is exactly thefield_type provided. The input expression iscoerced tofield_type if the two types aren't the same, and an error isproduced if the types aren't compatible.AS alias isn't allowed on the inputexpressions. The number of expressions must match the number of fields in thetype, and the expression types must be coercible or literal-coercible to thefield types.

Examples

Limited comparisons for structs

Structs can be directly compared using equality operators:

• Equal (=)
• Not Equal (!= or<>)
• [NOT]IN

Notice, though, that these direct equality comparisons compare the fields ofthe struct pairwise in ordinal order ignoring any field names. If instead youwant to compare identically named fields of a struct, you can compare theindividual fields directly.

Time type

A time value represents a time of day, as might be displayed on a clock,independent of a specific date and time zone. To representan absolute point in time, use atimestamp.

Canonical format

[H]H:[M]M:[S]S[.F]

• [H]H: One or two digit hour (valid values from 00 to 23).
• [M]M: One or two digit minutes (valid values from 00 to 59).
• [S]S: One or two digit seconds (valid values from 00 to 60).
• [.F]: Up to six fractional digits (microsecondprecision).

To learn more about the literal representation of a time type,seeTime literals.

Timestamp type

A timestamp value represents an absolute point in time,independent of any time zone or convention such as daylight saving time (DST),withmicrosecond precision.

A timestamp is typically represented internally as the number of elapsedmicroseconds since a fixed initial point in time.

Note that a timestamp itself doesn't have a time zone; it represents the sameinstant in time globally. However, thedisplay of a timestamp for humanreadability usually includes a Gregorian date, a time, and a time zone, in animplementation-dependent format. For example, the displayed values "2020-01-0100:00:00 UTC", "2019-12-31 19:00:00 America/New_York", and "2020-01-01 05:30:00Asia/Kolkata" all represent the same instant in time and therefore represent thesame timestamp value.

• To represent a Gregorian date as it might appear on a calendar(a civil date), use adate value.
• To represent a time as it might appear on a clock (a civil time),use atime value.
• To represent a Gregorian date and time as they might appear on a watch,use adatetime value.

Canonical format

The canonical format for a timestamp literal has the following parts:

{civil_date_part[time_part[time_zone]]|civil_date_part[time_part[time_zone_offset]]|civil_date_part[time_part[utc_time_zone]]}civil_date_part:YYYY-[M]M-[D]Dtime_part:{|T|t}[H]H:[M]M:[S]S[.F]

• YYYY: Four-digit year.
• [M]M: One or two digit month.
• [D]D: One or two digit day.
• { |T|t}: A space or aT ort separator. TheT andtseparators are flags for time.
• [H]H: One or two digit hour (valid values from 00 to 23).
• [M]M: One or two digit minutes (valid values from 00 to 59).
• [S]S: One or two digit seconds (valid values from 00 to 60).
• [.F]: Up to six fractional digits(microsecond precision).
• [time_zone]: String representing the time zone. When a timezone isn't explicitly specified, the default time zone,UTC, is used. For details, seetimezones.
• [time_zone_offset]: String representing the offset from theCoordinated Universal Time (UTC) time zone. For details, seetime zones.
• [utc_time_zone]: String representing the Coordinated UniversalTime (UTC), usually the letterZ orz. For details, seetime zones.

To learn more about the literal representation of a timestamp type,seeTimestamp literals.

Time zones

A time zone is used when converting from a civil date or time (as might appearon a calendar or clock) to a timestamp (an absolute time), or vice versa. Thisincludes the operation of parsing a string containing a civil date and time like"2020-01-01 00:00:00" and converting it to a timestamp. The resulting timestampvalue itself doesn't store a specific time zone, because it represents oneinstant in time globally.

Time zones are represented by strings in one of these canonical formats:

• Offset from Coordinated Universal Time (UTC), or the letterZ orz forUTC.
• Time zone name from thetz database.BigQuery syncs intermittently with the database.

The following timestamps are identical because the time zone offsetforAmerica/Los_Angeles is-08 for the specified date and time.

SELECTUNIX_MILLIS(TIMESTAMP'2008-12-25 15:30:00 America/Los_Angeles')ASmillis;

SELECTUNIX_MILLIS(TIMESTAMP'2008-12-25 15:30:00-08:00')ASmillis;

Specify Coordinated Universal Time (UTC)

You can specify UTC using the following suffix:

{Z|z}

You can also specify UTC using the following time zone name:

{Etc/UTC}

TheZ suffix is a placeholder that implies UTC when converting anRFC3339-format value to aTIMESTAMP value. The valueZ isn'ta valid time zone for functions that accept a time zone. If you're specifying atime zone, or you're unsure of the format to use to specify UTC, we recommendusing theEtc/UTC time zone name.

TheZ suffix isn't case sensitive. When using theZ suffix, no space isallowed between theZ and the rest of the timestamp. The following areexamples of using theZ suffix and theEtc/UTC time zone name:

SELECTTIMESTAMP'2014-09-27T12:30:00.45Z'SELECTTIMESTAMP'2014-09-27 12:30:00.45z'SELECTTIMESTAMP'2014-09-27T12:30:00.45 Etc/UTC'

Specify an offset from Coordinated Universal Time (UTC)

You can specify the offset from UTC using the following format:

{+|-}H[H][:M[M]]

Examples:

-08:00-8:15+3:00+07:30-7

When using this format, no space is allowed between the time zone and the restof the timestamp.

2014-09-2712:30:00.45-8:00

Time zone name

Format:

tz_identifier

A time zone name is a tz identifier from thetz database.For a less comprehensive but simpler reference, see theList of tz database time zones on Wikipedia.

Examples:

America/Los_AngelesAmerica/Argentina/Buenos_AiresEtc/UTCPacific/Auckland

When using a time zone name, a space is required between the name and the restof the timestamp:

2014-09-2712:30:00.45America/Los_Angeles

Note that not all time zone names are interchangeable even if they do happen toreport the same time during a given part of the year. For example,America/Los_Angeles reports the same time asUTC-7:00 during daylightsaving time (DST), but reports the same time asUTC-8:00 outside of DST.

If a time zone isn't specified, the default time zone value is used.

Leap seconds

A timestamp is simply an offset from 1970-01-01 00:00:00 UTC, assuming there areexactly 60 seconds per minute. Leap seconds aren't represented as part of astored timestamp.

If the input contains values that use ":60" in the seconds field to represent aleap second, that leap second isn't preserved when converting to a timestampvalue. Instead that value is interpreted as a timestamp with ":00" in theseconds field of the following minute.

Leap seconds don't affect timestamp computations. All timestamp computationsare done using Unix-style timestamps, which don't reflect leap seconds. Leapseconds are only observable through functions that measure real-world time. Inthese functions, it's possible for a timestamp second to be skipped or repeatedwhen there is a leap second.

Daylight saving time

A timestamp is unaffected by daylight saving time (DST) because it represents apoint in time. When you display a timestamp as a civil time,with a timezone that observes DST, the following rules apply:

• During the transition from standard time to DST, one hour is skipped. Acivil time from the skipped hour is treated the same as if it were writtenan hour later. For example, in theAmerica/Los_Angeles time zone, the hourbetween 2 AM and 3 AM on March 10, 2024 is skipped on a clock. The times2:30 AM and 3:30 AM on that date are treated as the same point in time:

  SELECTFORMAT_TIMESTAMP("%c %Z","2024-03-10 02:30:00 America/Los_Angeles","UTC")AStwo_thirty,FORMAT_TIMESTAMP("%c %Z","2024-03-10 03:30:00 America/Los_Angeles","UTC")ASthree_thirty;/*------------------------------+------------------------------+ | two_thirty                   | three_thirty                 | +------------------------------+------------------------------+ | Sun Mar 10 10:30:00 2024 UTC | Sun Mar 10 10:30:00 2024 UTC | +------------------------------+------------------------------*/

• When there's ambiguity in how to represent a civil time in a particulartimezone because of DST, the later time is chosen:

  SELECTFORMAT_TIMESTAMP("%c %Z","2024-03-10 10:30:00 UTC","America/Los_Angeles")asten_thirty;/*--------------------------------+ | ten_thirty                     | +--------------------------------+ | Sun Mar 10 03:30:00 2024 UTC-7 | +--------------------------------*/

• During the transition from DST to standard time, one hour is repeated. Acivil time that shows a time during that hour is treated as if it's theearlier instance of that time. For example, in theAmerica/Los_Angeles timezone, the hour between 1 AM and 2 AM on November 3, 2024, is repeated on aclock. The time 1:30 AM on that date is treated as the earlier (DST) instanceof that time.

  SELECTFORMAT_TIMESTAMP("%c %Z","2024-11-03 01:30:00 America/Los_Angeles","UTC")asone_thirty,FORMAT_TIMESTAMP("%c %Z","2024-11-03 02:30:00 America/Los_Angeles","UTC")astwo_thirty;/*------------------------------+------------------------------+ | one_thirty                   | two_thirty                   | +------------------------------+------------------------------+ | Sun Nov 3 08:30:00 2024 UTC  | Sun Nov 3 10:30:00 2024 UTC  | +------------------------------+------------------------------*/