Data types

This page provides an overview of all ZetaSQL data types, including information about their value domains. For information on data type literals and constructors, see Lexical Structure and Syntax.

Data type list

Name	Summary
Array type	An ordered list of zero or more elements of non-array values. SQL type name: `ARRAY`
Boolean type	A value that can be either `TRUE` or `FALSE`. SQL type name: `BOOL` SQL aliases: `BOOLEAN`
Bytes type	Variable-length binary data. SQL type name: `BYTES`
Date type	A Gregorian calendar date, independent of time zone. SQL type name: `DATE`
Datetime type	A Gregorian date and a time, as they might be displayed on a watch, independent of time zone. SQL type name: `DATETIME`
Enum type	Named type that enumerates a list of possible values. SQL type name: `ENUM`
Geography type	A collection of points, linestrings, and polygons, which is represented as a point set, or a subset of the surface of the Earth. SQL type name: `GEOGRAPHY`
Graph element type	An element in a property graph. SQL type name: `GRAPH_ELEMENT`
Interval type	A duration of time, without referring to any specific point in time. SQL type name: `INTERVAL`
JSON type	Represents JSON, a lightweight data-interchange format. SQL type name: `JSON`
Numeric types	A numeric value. Several types are supported. <p> A 32-bit integer.<br/> SQL type name: <code>INT32</code> </p> <p> An unsigned 32-bit integer.<br/> SQL type name: <code>UINT32</code> </p> <p> A 64-bit integer.<br/> SQL type name: <code>INT64</code> </p> <p> An unsigned 64-bit integer.<br/> SQL type name: <code>UINT64</code> </p> <p> A decimal value with precision of 38 digits.<br/> SQL type name: <code>NUMERIC</code> <br/> SQL aliases: <code>DECIMAL</code> </p> <p> A decimal value with precision of 76.76 digits (the 77th digit is partial).<br/> SQL type name: <code>BIGNUMERIC</code> <br/> SQL aliases: <code>BIGDECIMAL</code> </p> <p> An approximate single precision numeric value.<br/> SQL type name: <code>FLOAT</code> <br/> SQL aliases: <code>FLOAT32</code> </p> <p> An approximate double precision numeric value.<br/> SQL type name: <code>DOUBLE</code> <br/> SQL aliases: <code>FLOAT64</code> </p>
Protocol buffer type	A protocol buffer. SQL type name: `PROTO`
Range type	Contiguous range between two dates, datetimes, or timestamps. SQL type name: `RANGE`
String type	Variable-length character data. SQL type name: `STRING`
Struct type	Container of ordered fields. SQL type name: `STRUCT`
Time type	A time of day, as might be displayed on a clock, independent of a specific date and time zone. SQL type name: `TIME`
Timestamp type	A timestamp value represents an absolute point in time, independent of any time zone or convention such as daylight saving time (DST). SQL type name: `TIMESTAMP`

Data type properties

When storing and querying data, it is helpful to keep the following data type properties in mind:

Nullable data types

For nullable data types, NULL is a valid value. Currently, all existing data types are nullable, except for GRAPH_ELEMENT. Conditions apply for arrays.

Orderable data types

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

PROTO
STRUCT
GEOGRAPHY
JSON
GRAPH_ELEMENT

Ordering `NULL`s

In the context of the ORDER BY clause, NULLs are the minimum possible value; that is, NULLs appear first in ASC sorts and last in DESC sorts.

NULL values can be specified as the first or last values for a column irrespective of ASC or DESC by using the NULLS FIRST or NULLS LAST modifiers respectively.

To learn more about using ASC, DESC, NULLS FIRST and NULLS LAST, see the ORDER BY clause.

Ordering floating points

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

NULL
NaN — All NaN values are considered equal when sorting.
-inf
Negative numbers
0 or -0 — All zero values are considered equal when sorting.
Positive numbers
+inf

Ordering arrays

ARRAY<T> is orderable if its type, T, is orderable. Empty arrays are sorted before non-empty arrays. Non-empty arrays are sorted lexicographically by element. An array that is a strict prefix of another array orders less than the longer array.

Lexicographical ordering for arrays first compares the elements of each array from the first element to the last. If an element orders before a corresponding element in another array, then the arrays are ordered accordingly. Subsequent array elements are ignored.

For example:

WITH
  t AS (
    SELECT [1, 2] a UNION ALL
    SELECT [1, NULL] a UNION ALL
    SELECT [0, 1] UNION ALL
    SELECT [0, 1, 4] UNION ALL
    SELECT [0, 1, 5] UNION ALL
    SELECT [3] UNION ALL
    SELECT [] UNION ALL
    SELECT CAST(NULL AS ARRAY<INT64>)
  )
SELECT a FROM t ORDER BY a

/*-----------*
 | a         |
 +-----------+
 | NULL      |
 | []        |
 | [0, 1]    |
 | [0, 1, 4] |
 | [0, 1, 5] |
 | [1, NULL] |
 | [1, 2]    |
 | [3]       |
 *-----------*/

Groupable data types

Groupable data types can generally appear in an expression following GROUP BY, DISTINCT, and PARTITION BY. All data types are supported except for:

PROTO
GEOGRAPHY
JSON
GRAPH_ELEMENT

Grouping with floating point types

Groupable floating point types can appear in an expression following GROUP BY and DISTINCT. PARTITION BY expressions cannot include floating point types.

Special floating point values are grouped in the following way, including both grouping done by a GROUP BY clause and grouping done by the DISTINCT keyword:

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

Grouping with arrays

An ARRAY type is groupable if its element type is groupable.

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

The two arrays are both NULL.
The two arrays have the same number of elements and all corresponding elements are in the same groups.

Grouping with structs

A STRUCT type is groupable if its field types are groupable.

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

The two structs are both NULL.
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:

PROTO
GEOGRAPHY
JSON

Notes:

Equality comparisons for array data types are supported as long as the element types are the same, and the element types are comparable. Less than and greater than comparisons are not supported.
Equality comparisons for structs are supported field by field, in field order. Field names are ignored. Less than and greater than comparisons are not supported.
To compare geography values, use ST_Equals.
When comparing ranges, the lower bounds are compared. If the lower bounds are equal, 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 are NULL, the comparison is TRUE.
- An UNBOUNDED bound is treated as a NULL bound.
All types that support comparisons can be used in a JOIN condition. See JOIN Types for an explanation of join conditions.

Collatable data types

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

String
String fields in a struct
String elements in an array

Array type

Name	Description
`ARRAY`	Ordered list of zero or more elements of any non-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 are not allowed. Queries that would produce an array of arrays will return an error. Instead, a struct must be inserted between the arrays using the SELECT AS STRUCT construct.

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

To learn more about using arrays in ZetaSQL, see Work with arrays.

`NULL`s and the array type

Currently, ZetaSQL has the following rules with respect to NULLs and arrays:

An array can be NULL.

For example:

SELECT CAST(NULL AS ARRAY<INT64>) IS NULL AS array_is_null;

/*---------------*
 | array_is_null |
 +---------------+
 | TRUE          |
 *---------------*/

ZetaSQL translates a NULL array into an empty array in the query result, although inside the query, NULL and empty arrays are two distinct values.

For example:

WITH Items AS (
  SELECT [] AS numbers, "Empty array in query" AS description UNION ALL
  SELECT CAST(NULL AS ARRAY<INT64>), "NULL array in query")
SELECT numbers, description, numbers IS NULL AS numbers_null
FROM Items;

/*---------+----------------------+--------------*
 | numbers | description          | numbers_null |
 +---------+----------------------+--------------+
 | []      | Empty array in query | false        |
 | []      | NULL array in query  | true         |
 *---------+----------------------+--------------*/

When you write a NULL array to a table, it is converted to an empty array. If you write Items to a table from the previous query, then each array is written as an empty array:

SELECT numbers, description, numbers IS NULL AS numbers_null
FROM Items;

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

Declaring an array type

ARRAY<T>

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

Examples

Type Declaration	Meaning
`ARRAY<INT64>`	Simple array of 64-bit integers.
`ARRAY<STRUCT<INT64, INT64>>`	An array of structs, each of which contains two 64-bit integers.
`ARRAY<ARRAY<INT64>>` (not supported)	This is an invalid type declaration which is included here just in case you came looking for how to create a multi-level array. Arrays cannot contain arrays directly. Instead see the next example.
`ARRAY<STRUCT<ARRAY<INT64>>>`	An array of arrays of 64-bit integers. Notice that there is a struct between the two arrays because arrays cannot hold other arrays directly.

Constructing an array

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

Using array literals

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

SELECT [1, 2, 3] AS numbers;

SELECT ["apple", "pear", "orange"] AS fruit;

SELECT [true, false, true] AS booleans;

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

SELECT [a, b, c]
FROM
  (SELECT 5 AS a,
          37 AS b,
          406 AS c);

SELECT [a, b, c]
FROM
  (SELECT CAST(5 AS INT64) AS a,
          CAST(37 AS DOUBLE) AS b,
          406 AS c);

Notice that the second example contains three expressions: one that returns an INT64, one that returns a DOUBLE, and one that declares a literal. This expression works because all three expressions share DOUBLE as a supertype.

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

SELECT ARRAY<DOUBLE>[1, 2, 3] AS floats;

Arrays of most data types, such as INT64 or STRING, don't require that you declare them first.

SELECT [1, 2, 3] AS numbers;

You can write an empty array of a specific type using ARRAY<type>[]. You can also write an untyped empty array using [], in which case ZetaSQL attempts to infer the array type from the surrounding context. If ZetaSQL cannot infer a type, the default type ARRAY<INT64> is used.

Using generated values

You can also construct an ARRAY with generated values.

Generating arrays of integers

GENERATE_ARRAY generates 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 odd integers from 11 to 33, inclusive:

SELECT GENERATE_ARRAY(11, 33, 2) AS odds;

/*--------------------------------------------------*
 | 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 a negative step value:

SELECT GENERATE_ARRAY(21, 14, -1) AS countdown;

/*----------------------------------*
 | countdown                        |
 +----------------------------------+
 | [21, 20, 19, 18, 17, 16, 15, 14] |
 *----------------------------------*/

Generating arrays of dates

GENERATE_DATE_ARRAY generates an array of DATEs from a starting and ending DATE and a step INTERVAL.

You can generate a set of DATE values using GENERATE_DATE_ARRAY. For example, this query returns the current DATE and the following DATEs at 1 WEEK intervals up to and including a later DATE:

SELECT
  GENERATE_DATE_ARRAY('2017-11-21', '2017-12-31', INTERVAL 1 WEEK)
    AS date_array;

/*--------------------------------------------------------------------------*
 | date_array                                                               |
 +--------------------------------------------------------------------------+
 | [2017-11-21, 2017-11-28, 2017-12-05, 2017-12-12, 2017-12-19, 2017-12-26] |
 *--------------------------------------------------------------------------*/

Boolean type

Name	Description
`BOOL` `BOOLEAN`	Boolean values are represented by the keywords `TRUE` and `FALSE` (case-insensitive).

BOOLEAN is an alias for BOOL.

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

NULL
FALSE
TRUE

Bytes type

Name	Description
`BYTES`	Variable-length binary data.

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

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

Date type

Name	Range
`DATE`	0001-01-01 to 9999-12-31.

The date type represents a Gregorian calendar date, independent of time zone. A date value does not represent a specific 24-hour time period. Rather, a given date value represents a different 24-hour period when interpreted in different time zones, and may represent a shorter or longer day during daylight saving time (DST) transitions. To represent an absolute point in time, use a timestamp.

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, see Date literals.

Datetime type

Name	Range
`DATETIME`	`0001-01-01 00:00:00 to 9999-12-31 23:59:59.999999999<br/> <hr/> 0001-01-01 00:00:00 to 9999-12-31 23:59:59.999999<br/>`

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. The range of subsecond precision is determined by the SQL engine. To represent an absolute point in time, use a timestamp.

Canonical format

civil_date_part[time_part]

civil_date_part:
    YYYY-[M]M-[D]D

time_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 a T or t separator. The T and t separators 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 nine fractional digits (nanosecond precision).

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

Enum type

Name	Description
`ENUM`	Named type that maps string constants to `INT32` constants.

An enum is a named type that enumerates a list of possible values, each of which contains:

An integer value: Integers are used for comparison and ordering enum values. There is no requirement that these integers start at zero or that they be contiguous.
A string value for its name: Strings are case sensitive. In the case of protocol buffer open enums, this name is optional.
Optional alias values: One or more additional string values that act as aliases.

Enum values are referenced using their integer value or their string value. You reference an enum type, such as when using CAST, by using its fully qualified name.

You cannot create new enum types using ZetaSQL.

To learn more about the literal representation of an enum type, see Enum literals.

Geography type

Name	Description
`GEOGRAPHY`	A collection of points, linestrings, and polygons, which is represented as a point set, or a subset of the surface of the Earth.

The geography type is based on the OGC Simple Features specification (SFS){: class=external target=_blank }, and can contain the following objects:

Geography object Description

Point

A single location in coordinate space known as a point. A point has an x-coordinate value and a y-coordinate value, where the x-coordinate is longitude and the y-coordinate is latitude of the point on the WGS84 reference ellipsoid.

    </p>
    <p>
      Syntax:

POINT(x_coordinate y_coordinate)

      Examples:

POINT(32 210)

POINT EMPTY

    </p>
  </td>
</tr>
<tr>
  <td><code>LineString</code></td>
  <td>
    <p>
      Represents a linestring, which is a one-dimensional geometric object,
      with a sequence of points and geodesic edges between them.
    </p>
    <p>
      Syntax:

LINESTRING(point[, ...])

      Examples:

LINESTRING(1 1, 2 1, 3.1 2.88, 3 -3)

LINESTRING EMPTY

    </p>
  </td>
</tr>
<tr>
  <td><code>Polygon</code></td>
  <td>
    <p>
      A polygon, which is represented as a planar surface defined by 1
      exterior boundary and 0 or more interior boundaries. Each
      interior boundary defines a hole in the polygon. The boundary loops of
      polygons are oriented so that if you traverse the boundary vertices in
      order, the interior of the polygon is on the left.
    </p>
    <p>
      Syntax:

POLYGON(interior_ring[, ...])

interior_ring:
  (point[, ...])

      Examples:

POLYGON((0 0, 2 2, 2 0, 0 0), (2 2, 3 4, 2 4, 2 2))

POLYGON EMPTY

    </p>
  </td>
</tr>
<tr>
  <td><code>MultiPoint</code></td>
  <td>
    <p>
      A collection of points.
    </p>
    <p>
      Syntax:

MULTIPOINT(point[, ...])

      Examples:

MULTIPOINT(0 32, 123 9, 48 67)

MULTIPOINT EMPTY

    </p>
  </td>
</tr>
<tr>
  <td><code>MultiLineString</code></td>
  <td>
    <p>
      Represents a multilinestring, which is a collection of linestrings.
    </p>
    <p>
      Syntax:

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

      Examples:

MULTILINESTRING((2 2, 3 4), (5 6, 7 7))

MULTILINESTRING EMPTY

    </p>
  </td>
</tr>
<tr>
  <td><code>MultiPolygon</code></td>
  <td>
    <p>
      Represents a multipolygon, which is a collection of polygons.
    </p>
    <p>
      Syntax:

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

      Examples:

MULTIPOLYGON(((0 -1, 1 0, 1 1, 0 -1)), ((0 0, 2 2, 3 0, 0 0), (2 2, 3 4, 2 4, 1 9)))

MULTIPOLYGON EMPTY

    </p>
  </td>
</tr>
<tr>
  <td><code>GeometryCollection</code></td>
  <td>
    <p>
      Represents a geometry collection with elements of different dimensions
      or an empty geography.
    </p>
    <p>
      Syntax:

GEOMETRYCOLLECTION(geography_object[, ...])

      Examples:

GEOMETRYCOLLECTION(MULTIPOINT(-1 2, 0 12), LINESTRING(-2 4, 0 6))

GEOMETRYCOLLECTION EMPTY

    </p>
  </td>
</tr>

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

The geography that contains no points, linestrings or polygons is called an empty geography. An empty geography is not associated with a particular geometry shape. For example, the following query produces the same results:

SELECT
  ST_GEOGFROMTEXT('POINT EMPTY') AS a,
  ST_GEOGFROMTEXT('GEOMETRYCOLLECTION EMPTY') AS b

/*--------------------------+--------------------------*
 | a                        | b                        |
 +--------------------------+--------------------------+
 | GEOMETRYCOLLECTION EMPTY | GEOMETRYCOLLECTION EMPTY |
 *--------------------------+--------------------------*/

The structure of compound geometry objects is not preserved if a simpler type can be produced. For example, in column b, GEOMETRYCOLLECTION with (POINT(1 1) and POINT(2 2) is converted into the simplest possible geometry, MULTIPOINT(1 1, 2 2).

SELECT
  ST_GEOGFROMTEXT('MULTIPOINT(1 1, 2 2)') AS a,
  ST_GEOGFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1), POINT(2 2))') AS b

/*----------------------+----------------------*
 | a                    | b                    |
 +----------------------+----------------------+
 | MULTIPOINT(1 1, 2 2) | MULTIPOINT(1 1, 2 2) |
 *----------------------+----------------------*/

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

Graph element type

Name	Description
`GRAPH_ELEMENT`	An element in a property graph.

A variable with a GRAPH_ELEMENT type is produced by a graph query. The generated type has this format:

GRAPH_ELEMENT<T>

A graph element can be one of two kinds: a node or edge. A graph element is similar to the struct type, except that fields are graph properties, and you can only access graph properties by name. A graph element can represent nodes or edges from multiple node or edge tables if multiple such tables match the given label expression.

Example

In the following example, n represents a graph element in the FinGraph property graph:

GRAPH FinGraph
MATCH (n:Person)
RETURN n.name

Interval type

Name	Range
`INTERVAL`	-10000-0 -3660000 -87840000:0:0 to 10000-0 3660000 87840000:0:0

An INTERVAL object represents duration or amount of time, without referring to 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 nine fractional digits (nanosecond precision)

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

Constructing an interval

You can construct an interval with an interval literal that supports a single datetime part or a datetime part range.

Construct an interval with a single datetime part

INTERVAL int64_expression datetime_part

You can construct an INTERVAL object with an INT64 expression and one interval-supported datetime part. For example:

-- 1 year, 0 months, 0 days, 0 hours, 0 minutes, and 0 seconds (1-0 0 0:0:0)
INTERVAL 1 YEAR
INTERVAL 4 QUARTER
INTERVAL 12 MONTH

-- 0 years, 3 months, 0 days, 0 hours, 0 minutes, and 0 seconds (0-3 0 0:0:0)
INTERVAL 1 QUARTER
INTERVAL 3 MONTH

-- 0 years, 0 months, 42 days, 0 hours, 0 minutes, and 0 seconds (0-0 42 0:0:0)
INTERVAL 6 WEEK
INTERVAL 42 DAY

-- 0 years, 0 months, 0 days, 25 hours, 0 minutes, and 0 seconds (0-0 0 25:0:0)
INTERVAL 25 HOUR
INTERVAL 1500 MINUTE
INTERVAL 90000 SECOND

-- 0 years, 0 months, 0 days, 1 hours, 30 minutes, and 0 seconds (0-0 0 1:30:0)
INTERVAL 90 MINUTE

-- 0 years, 0 months, 0 days, 0 hours, 1 minutes, and 30 seconds (0-0 0 0:1:30)
INTERVAL 90 SECOND

-- 0 years, 0 months, -5 days, 0 hours, 0 minutes, and 0 seconds (0-0 -5 0:0:0)
INTERVAL -5 DAY

For additional examples, see Interval literals.

Construct an interval with a datetime part range

INTERVAL datetime_parts_string starting_datetime_part TO ending_datetime_part

You can construct an INTERVAL object with a STRING that contains the datetime parts that you want to include, a starting datetime part, and an ending datetime part. The resulting INTERVAL object only includes datetime parts in the specified range.

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

Datetime part string	Datetime parts	Example
`Y-M`	`YEAR TO MONTH`	`INTERVAL '2-11' YEAR TO MONTH`
`Y-M D`	`YEAR TO DAY`	`INTERVAL '2-11 28' YEAR TO DAY`
`Y-M D H`	`YEAR TO HOUR`	`INTERVAL '2-11 28 16' YEAR TO HOUR`
`Y-M D H:M`	`YEAR TO MINUTE`	`INTERVAL '2-11 28 16:15' YEAR TO MINUTE`
`Y-M D H:M:S`	`YEAR TO SECOND`	`INTERVAL '2-11 28 16:15:14' YEAR TO SECOND`
`M D`	`MONTH TO DAY`	`INTERVAL '11 28' MONTH TO DAY`
`M D H`	`MONTH TO HOUR`	`INTERVAL '11 28 16' MONTH TO HOUR`
`M D H:M`	`MONTH TO MINUTE`	`INTERVAL '11 28 16:15' MONTH TO MINUTE`
`M D H:M:S`	`MONTH TO SECOND`	`INTERVAL '11 28 16:15:14' MONTH TO SECOND`
`D H`	`DAY TO HOUR`	`INTERVAL '28 16' DAY TO HOUR`
`D H:M`	`DAY TO MINUTE`	`INTERVAL '28 16:15' DAY TO MINUTE`
`D H:M:S`	`DAY TO SECOND`	`INTERVAL '28 16:15:14' DAY TO SECOND`
`H:M`	`HOUR TO MINUTE`	`INTERVAL '16:15' HOUR TO MINUTE`
`H:M:S`	`HOUR TO SECOND`	`INTERVAL '16:15:14' HOUR TO SECOND`
`M:S`	`MINUTE TO SECOND`	`INTERVAL '15:14' MINUTE TO SECOND`

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' MONTH TO HOUR

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

For additional examples, see Interval 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 to 3 months, M.
MONTH: Number of months, M. Each 12 months is converted to 1 year.
WEEK: Number of weeks; Each week is converted to 7 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. Each 60 minutes is converted to 1 hour.
SECOND: Number of seconds, S. Each 60 seconds is converted to 1 minute. Can include up to nine fractional digits (nanosecond precision).
MILLISECOND: Number of milliseconds.
MICROSECOND: Number of microseconds.
NANOSECOND: Number of nanoseconds.

JSON type

Name	Description
`JSON`	Represents JSON, a lightweight data-interchange format.

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

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

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

Numeric types

Numeric types include the following types:

INT32
UINT32
INT64
UINT64
NUMERIC with alias DECIMAL
BIGNUMERIC with alias BIGDECIMAL
FLOAT
DOUBLE

Integer types

Integers are numeric values that do not have fractional components.

Name	Range
`INT32`	-2,147,483,648 to 2,147,483,647
`UINT32`	0 to 4,294,967,295
`INT64`	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
`UINT64`	0 to 18,446,744,073,709,551,615

To learn more about the literal representation of an integer type, see Integer 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 is how many of these digits appear after the decimal point.

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

Name	Precision, Scale, and Range
`NUMERIC` `DECIMAL`	Precision: 38 Scale: 9 Minimum value greater than 0 that can be handled: 1e-9 Min: -9.9999999999999999999999999999999999999E+28 Max: 9.9999999999999999999999999999999999999E+28
`BIGNUMERIC` `BIGDECIMAL`	Precision: 76.76 (the 77th digit is partial) Scale: 38 Minimum value greater than 0 that can be handled: 1e-38 Min: -5.7896044618658097711785492504343953926634992332820282019728792003956564819968E+38 Max: 5.7896044618658097711785492504343953926634992332820282019728792003956564819967E+38

DECIMAL is an alias for NUMERIC.

BIGDECIMAL is an alias for BIGNUMERIC.

To learn more about the literal representation of a NUMERIC type, see NUMERIC literals.

To learn more about the literal representation of a BIGNUMERIC type, see BIGNUMERIC literals.

Floating point types

Floating point values are approximate numeric values with fractional components.

Name	Description
`FLOAT` `FLOAT32`	Single precision (approximate) numeric values.
`DOUBLE` `FLOAT64`	Double precision (approximate) numeric values.

FLOAT32 is an alias for FLOAT.

FLOAT64 is an alias for DOUBLE.

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

Floating point semantics

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

Arithmetic operators provide standard IEEE-754 behavior for all finite input values that produce finite output and for all operations for which at least one input is non-finite.

Function calls and operators return an overflow error if the input is finite but the output would be non-finite. If the input contains non-finite values, the output can be non-finite. In general functions do not introduce NaNs or +/-inf. However, specific functions like IEEE_DIVIDE can return non-finite values on finite input. All such cases are noted explicitly in Mathematical functions.

Floating point values are approximations.

The binary format used to represent floating point values can only represent a subset of the numbers between the most positive number and most negative number in the value range. This enables efficient handling of a much larger range than would be possible otherwise. Numbers that are not exactly representable are approximated by utilizing a close value instead. For example, 0.1 cannot be represented as an integer scaled by a power of 2. When this value is displayed as a string, it is rounded to a limited number of digits, and the value approximating 0.1 might appear as "0.1", hiding the fact that the value is not precise. In other situations, the approximation can be visible.
Summation of floating point values might produce surprising results because of limited precision. For example, (1e30 + 1) - 1e30 = 0, while (1e30 - 1e30) + 1 = 1.0. This is because the floating point value does not have enough precision to represent (1e30 + 1), and the result is rounded to 1e30. This example also shows that the result of the SUM aggregate function of floating points values depends on the order in which the values are accumulated. In general, this order is not deterministic and therefore the result is not deterministic. Thus, the resulting SUM of floating point values might not be deterministic and two executions of the same query on the same tables might produce different results.
If the above points are concerning, use a decimal type instead.

Mathematical function examples

Left Term	Operator	Right Term	Returns
Any value	`+`	`NaN`	`NaN`
1.0	`+`	`+inf`	`+inf`
1.0	`+`	`-inf`	`-inf`
`-inf`	`+`	`+inf`	`NaN`
Maximum `DOUBLE` value	`+`	Maximum `DOUBLE` value	Overflow error
Minimum `DOUBLE` value	`/`	2.0	0.0
1.0	`/`	`0.0`	"Divide by zero" error

Comparison operators provide standard IEEE-754 behavior for floating point input.

Comparison operator examples

Left Term	Operator	Right Term	Returns
`NaN`	`=`	Any value	`FALSE`
`NaN`	`<`	Any value	`FALSE`
Any value	`<`	`NaN`	`FALSE`
-0.0	`=`	0.0	`TRUE`
-0.0	`<`	0.0	`FALSE`

For more information on how these values are ordered and grouped so they can be compared, see Ordering floating point values.

Protocol buffer type

Name	Description
`PROTO`	An instance of protocol buffer.

Protocol buffers provide structured data types with a defined serialization format and cross-language support libraries. Protocol buffer message types can contain optional, required, or repeated fields, including nested messages. For more information, see the Protocol Buffers Developer Guide.

Protocol buffer message types behave similarly to struct types, and support similar operations like reading field values by name. Protocol buffer types are always named types, and can be referred to by their fully-qualified protocol buffer name (i.e. package.ProtoName). Protocol buffers support some additional behavior beyond structs, like default field values, defining a column type, and checking for the presence of optional fields.

Protocol buffer enum types are also available and can be referenced using the fully-qualified enum type name.

To learn more about using protocol buffers in ZetaSQL, see Work with protocol buffers.

Constructing a protocol buffer

You can construct a protocol buffer using the NEW operator or the SELECT AS typename statement. Regardless of the method that you choose, the resulting protocol buffer is the same.

`NEW protocol_buffer {...}` {: #using_new_map_constructor }

You can create a protocol buffer using the NEW operator with a map constructor:

NEW protocol_buffer {
  field_name: literal_or_expression
  field_name { ... }
  repeated_field_name: [literal_or_expression, ... ]
  map_field_name: [{key: literal_or_expression value: literal_or_expression}, ...],
  (extension_name): literal_or_expression
}

Where:

protocol_buffer: The full protocol buffer name including the package name.
field_name: The name of a field.
literal_or_expression: The field value.
map_field_name: The name of a map-typed field. The value is a list of key/value pair entries for the map.
extension_name: The name of the proto extension, including the package name.

Example

NEW zetasql.examples.astronomy.Planet {
  planet_name: 'Jupiter'
  facts: {
    length_of_day: 9.93
    distance_to_sun: 5.2 * ASTRONOMICAL_UNIT
    has_rings: TRUE
  }
  major_moons: [
    { moon_name: 'Io' },
    { moon_name: 'Europa' },
    { moon_name: 'Ganymede' },
    { moon_name: 'Callisto'}
  ]
  minor_moons: (
    SELECT ARRAY_AGG(moon_name)
    FROM SolarSystemMoons
    WHERE
      planet_name = 'Jupiter'
      AND circumference < 3121
  )
  count_of_space_probe_photos: (
    GALILEO_PHOTOS
    + JUNO_PHOTOS
    + NEW_HORIZONS_PHOTOS
    + CASSINI_PHOTOS
    + ULYSSES_PHOTOS
    + VOYAGER_1_PHOTOS
    + VOYAGER_2_PHOTOS
    + PIONEER_10_PHOTOS
    + PIONEER_11_PHOTOS
  ),
  (UniverseExtraInfo.extension) {
    ...
  }
}

NOTE: The syntax is very similar to the Protocol Buffer Text Format syntax. The differences are:

Values can be arbitrary SQL expressions instead of having to be literals.

Repeated fields are written as x_array: [1, 2, 3] instead of x_array: appearing multiple times.

Extensions use parentheses instead of square brackets.

When using this syntax, the following rules apply:

The field values must be expressions that are implicitly coercible or literal-coercible to the type of the corresponding protocol buffer field.
Commas between fields are optional.
Extension names must have parentheses around the path and must have a comma preceding the extension field (unless it is the first field).
A colon is required between field name and values unless the value is a map constructor.
The NEW protocol_buffer prefix is optional if the protocol buffer type can be inferred from the context.
The type of submessages inside the map constructor can be inferred.

Examples

Simple:

SELECT
  key,
  name,
  NEW zetasql.examples.music.Chart { rank: 1 chart_name: '2' }

Nested messages and arrays:

SELECT
  NEW zetasql.examples.music.Album {
    album_name: 'New Moon'
    singer {
      nationality: 'Canadian'
      residence: [ { city: 'Victoria' }, { city: 'Toronto' } ]
    }
    song: ['Sandstorm', 'Wait']
  }

With an extension field (note a comma is required before the extension field):

SELECT
  NEW zetasql.examples.music.Album {
    album_name: 'New Moon',
    (zetasql.examples.music.downloads): 30
  }

Non-literal expressions as values:

SELECT
  NEW zetasql.examples.music.Chart {
    rank: (SELECT COUNT(*) FROM TableName WHERE foo = 'bar')
    chart_name: CONCAT('best', 'hits')
  }

The following examples infers the protocol buffer data type from context:

From ARRAY constructor:

SELECT
  ARRAY<zetasql.examples.music.Chart>[
    { rank: 1 chart_name: '2' },
    { rank: 2 chart_name: '3' }]

From STRUCT constructor:

SELECT
  STRUCT<STRING, zetasql.examples.music.Chart, INT64>(
    'foo', { rank: 1 chart_name: '2' }, 7)[1]

From column names through SET:

Simple column:

UPDATE TableName SET proto_column = { rank: 1 chart_name: '2' }

Array column:

UPDATE TableName
SET proto_array_column = [
  { rank: 1 chart_name: '2' }, { rank: 2 chart_name: '3' }]

Struct column:

UPDATE TableName
SET proto_struct_column = ('foo', { rank: 1 chart_name: '2' }, 7)

From generated column names in CREATE:

CREATE TABLE TableName (
  proto_column zetasql.examples.music.Chart GENERATED ALWAYS AS (
    { rank: 1 chart_name: '2' }))

From column names in default values in CREATE:

CREATE TABLE TableName(
  proto_column zetasql.examples.music.Chart DEFAULT (
    { rank: 1 chart_name: '2' }))

From return types in SQL function body:

CREATE FUNCTION MyFunc()
RETURNS zetasql.examples.music.Chart
AS (
  { rank: 1 chart_name: '2' }
)

`NEW protocol_buffer (...)`

You can create a protocol buffer using the NEW operator with a parenthesized list of arguments and aliases to specify field names:

NEW protocol_buffer(field [AS alias], ...)

Example

SELECT
  key,
  name,
  NEW zetasql.examples.music.Chart(key AS rank, name AS chart_name)
FROM
  (SELECT 1 AS key, "2" AS name);

When using this syntax, the following rules apply:

All field expressions must have an explicit alias or end with an identifier. For example, the expression a.b.c has the implicit alias c.
NEW matches fields by alias to the field names of the protocol buffer. Aliases must be unique.
The expressions must be implicitly coercible or literal-coercible to the type of the corresponding protocol buffer field.

To create a protocol buffer with an extension, use this syntax:

NEW protocol_buffer(expression AS (path.to.extension), ...)

For path.to.extension, provide the path to the extension. Place the extension path inside parentheses.

expression provides the value to set for the extension. expression must be of the same type as the extension or coercible to that type.

Example:

SELECT
 NEW zetasql.examples.music.Album (
   album AS album_name,
   count AS (zetasql.examples.music.downloads)
 )
 FROM (SELECT 'New Moon' AS album, 30 AS count);

/*---------------------------------------------------*
 | $col1                                             |
 +---------------------------------------------------+
 | {album_name: 'New Moon' [...music.downloads]: 30} |
 *---------------------------------------------------*/

If path.to.extension points to a nested protocol buffer extension, expr1 provides an instance or a text format string of that protocol buffer.

Example:

SELECT
 NEW zetasql.examples.music.Album(
   'New Moon' AS album_name,
   NEW zetasql.examples.music.AlbumExtension(
    DATE(1956,1,1) AS release_date
   )
 AS (zetasql.examples.music.AlbumExtension.album_extension));

/*---------------------------------------------*
 | $col1                                       |
 +---------------------------------------------+
 | album_name: "New Moon"                      |
 | [...music.AlbumExtension.album_extension] { |
 |   release_date: -5114                       |
 | }                                           |
 *---------------------------------------------*/

`SELECT AS typename`

The SELECT AS typename statement can produce a value table where the row type is a specific named protocol buffer type.

SELECT AS does not support setting protocol buffer extensions. To do so, use the NEW keyword instead. For example, to create a protocol buffer with an extension, change a query like this:

SELECT AS typename field1, field2, ...

to a query like this:

SELECT AS VALUE NEW ProtoType(field1, field2, field3 AS (path.to.extension), ...)

Limited comparisons for protocol buffer values

Direct comparison of protocol buffers isn't supported. There are a few alternative solutions:

One way to compare protocol buffers is to do a pair-wise comparison between the fields of the protocol buffers. This can also be used to GROUP BY or ORDER BY protocol buffer fields.
To get a simple approximation comparison, cast protocol buffer to string. This applies lexicographical ordering for numeric fields.

Range type

Name	Range
`RANGE`	Contiguous range between two dates, datetimes, or timestamps. The lower and upper bound for the range are optional. The lower bound is inclusive and the upper bound is exclusive.

Declare a range type

A range type can be declared as follows:

Type Declaration	Meaning
`RANGE<DATE>`	Contiguous range between two dates.
`RANGE<DATETIME>`	Contiguous range between two datetimes.
`RANGE<TIMESTAMP>`	Contiguous range between two timestamps.

Construct a range

You can construct a range with the RANGE constructor or a range literal.

Construct a range with a constructor

You can construct a range with the RANGE constructor. To learn more, see RANGE.

Construct a range with a literal

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

RANGE<T> '[lower_bound, upper_bound)'

T: The type of range. This can be DATE, DATETIME, or TIMESTAMP.
lower_bound: The range starts from this value. This can be a date, datetime, or timestamp literal. If this value is UNBOUNDED or NULL, the range does not include a lower bound.
upper_bound: The range ends before this value. This can be a date, datetime, or timestamp literal. If this value is UNBOUNDED or NULL, the range does not include an upper bound.

T, lower_bound, and upper_bound must be of the same data type.

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

Additional details

The range type does not support arithmetic operators.

String type

Name	Description
`STRING`	Variable-length character (Unicode) data.

Input string values must be UTF-8 encoded and output string values will be UTF-8 encoded. Alternate encodings like CESU-8 and Modified UTF-8 are not treated as valid UTF-8.

All functions and operators that act on string values operate on Unicode characters rather than bytes. For example, functions like SUBSTR and LENGTH applied 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 are compared, the code points determine which characters are less than or greater than other characters.

Most functions on strings are also defined on bytes. The bytes version operates on raw bytes rather than Unicode characters. Strings and bytes are separate types that cannot be used interchangeably. There is no implicit casting in either direction. Explicit casting between string and bytes does UTF-8 encoding and decoding. Casting bytes to string returns an error if the bytes are not valid UTF-8.

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

Struct type

Name	Description
`STRUCT`	Container of ordered fields each with a type (required) and field name (optional).

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

Declaring a struct type

STRUCT<T>

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

Examples

Type Declaration	Meaning
`STRUCT<INT64>`	Simple struct with a single unnamed 64-bit integer field.
`STRUCT<x STRUCT<y INT64, z INT64>>`	A struct with a nested struct named `x` inside it. The struct `x` has two fields, `y` and `z`, both of which are 64-bit integers.
`STRUCT<inner_array ARRAY<INT64>>`	A struct containing an array named `inner_array` that holds 64-bit integer elements.

Constructing a struct

Tuple syntax

(expr1, expr2 [, ... ])

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

Examples

Syntax	Output Type	Notes
`(x, x+y)`	`STRUCT<?,?>`	If column names are used (unquoted strings), the struct field data type is derived from the column data type. `x` and `y` are columns, so the data types of the struct fields are derived from the column types and the output type of the addition operator.

This syntax can also be used with struct comparison for comparison expressions using multi-part keys, e.g., in a WHERE clause:

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

Typeless struct syntax

STRUCT( expr1 [AS field_name] [, ... ])

Duplicate field names are allowed. Fields without names are considered anonymous fields and cannot be referenced by name. struct values can be NULL, or can have NULL field values.

Examples

Syntax	Output Type
`STRUCT(1,2,3)`	`STRUCT<int64,int64,int64>`
`STRUCT()`	`STRUCT<>`
`STRUCT('abc')`	`STRUCT<string>`
`STRUCT(1, t.str_col)`	`STRUCT<int64, str_col string>`
`STRUCT(1 AS a, 'abc' AS b)`	`STRUCT<a int64, b string>`
`STRUCT(str_col AS abc)`	`STRUCT<abc string>`

Typed struct syntax

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

Typed syntax allows constructing structs with an explicit struct data type. The output type is exactly the field_type provided. The input expression is coerced to field_type if the two types are not the same, and an error is produced if the types are not compatible. AS alias is not allowed on the input expressions. The number of expressions must match the number of fields in the type, and the expression types must be coercible or literal-coercible to the field types.

Examples

Syntax	Output Type
`STRUCT<int64>(5)`	`STRUCT<int64>`
`STRUCT<date>("2011-05-05")`	`STRUCT<date>`
`STRUCT<x int64, y string>(1, t.str_col)`	`STRUCT<x int64, y string>`
`STRUCT<int64>(int_col)`	`STRUCT<int64>`
`STRUCT<x int64>(5 AS x)`	Error - Typed syntax does not allow `AS`

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 of the struct pairwise in ordinal order ignoring any field names. If instead you want to compare identically named fields of a struct, you can compare the individual fields directly.

Time type

<td>
    00:00:00 to 23:59:59.999999999<br/>
    <hr/>
    00:00:00 to 23:59:59.999999<br/>
</td>

Name	Range
`TIME`

A time value represents a time of day, as might be displayed on a clock, independent of a specific date and time zone. The range of subsecond precision is determined by the SQL engine. To represent an absolute point in time, use a timestamp.

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 nine fractional digits (nanosecond precision).

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

Timestamp type

<td>
  0001-01-01 00:00:00 to 9999-12-31 23:59:59.999999999 UTC<br/>
  <hr/>
  0001-01-01 00:00:00 to 9999-12-31 23:59:59.999999 UTC<br/>
</td>

Name	Range
`TIMESTAMP`

A timestamp value represents an absolute point in time, independent of any time zone or convention such as daylight saving time (DST), with microsecond or nanosecond precision. The range of subsecond precision is determined by the SQL engine.

A timestamp is typically represented internally as the number of elapsed nanoseconds or microseconds since a fixed initial point in time.

Note that a timestamp itself does not have a time zone; it represents the same instant in time globally. However, the display of a timestamp for human readability usually includes a Gregorian date, a time, and a time zone, in an implementation-dependent format. For example, the displayed values "2020-01-01 00:00:00 UTC", "2019-12-31 19:00:00 America/New_York", and "2020-01-01 05:30:00 Asia/Kolkata" all represent the same instant in time and therefore represent the same timestamp value.

To represent a Gregorian date as it might appear on a calendar (a civil date), use a date value.
To represent a time as it might appear on a clock (a civil time), use a time value.
To represent a Gregorian date and time as they might appear on a watch, use a datetime 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]D

time_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 a T or t separator. The T and t separators 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 nine fractional digits (nanosecond precision).
[time_zone]: String representing the time zone. When a time zone is not explicitly specified, the default time zone, which is implementation defined, is used. For details, see time zones.
[time_zone_offset]: String representing the offset from the Coordinated Universal Time (UTC) time zone. For details, see time zones.
[utc_time_zone]: String representing the Coordinated Universal Time (UTC), usually the letter Z or z. For details, see time zones.

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

Time zones

A time zone is used when converting from a civil date or time (as might appear on a calendar or clock) to a timestamp (an absolute time), or vice versa. This includes 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 timestamp value itself does not store a specific time zone, because it represents one instant in time globally.

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

Offset from Coordinated Universal Time (UTC), or the letter Z or z for UTC.
Time zone name from the tz database{: class=external target=_blank }.

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

SELECT UNIX_MILLIS(TIMESTAMP '2008-12-25 15:30:00 America/Los_Angeles') AS millis;

SELECT UNIX_MILLIS(TIMESTAMP '2008-12-25 15:30:00-08:00') AS millis;

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}

The Z suffix is a placeholder that implies UTC when converting an RFC 3339-format value to a TIMESTAMP value. The value Z isn't a valid time zone for functions that accept a time zone. If you're specifying a time zone, or you're unsure of the format to use to specify UTC, we recommend using the Etc/UTC time zone name.

The Z suffix isn't case sensitive. When using the Z suffix, no space is allowed between the Z and the rest of the timestamp. The following are examples of using the Z suffix and the Etc/UTC time zone name:

SELECT TIMESTAMP '2014-09-27T12:30:00.45Z'
SELECT TIMESTAMP '2014-09-27 12:30:00.45z'
SELECT TIMESTAMP '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 rest of the timestamp.

2014-09-27 12:30:00.45-8:00

Time zone name {: #time_zone_name}

Format:

tz_identifier

A time zone name is a tz identifier from the tz database{: class=external target=_blank }. For a less comprehensive but simpler reference, see the List of tz database time zones{: class=external target=_blank } on Wikipedia.

Examples:

America/Los_Angeles
America/Argentina/Buenos_Aires
Etc/UTC
Pacific/Auckland

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

2014-09-27 12:30:00.45 America/Los_Angeles

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

If a time zone is not 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 are exactly 60 seconds per minute. Leap seconds are not represented as part of a stored timestamp.

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

Leap seconds do not affect timestamp computations. All timestamp computations are done using Unix-style timestamps, which do not reflect leap seconds. Leap seconds are only observable through functions that measure real-world time. In these functions, it is possible for a timestamp second to be skipped or repeated when there is a leap second.

Files

data-types.md

Latest commit

History

data-types.md

File metadata and controls

Data types

Data type list

Data type properties

Nullable data types

Orderable data types

Ordering NULLs

Ordering floating points

Ordering arrays

Groupable data types

Grouping with floating point types

Grouping with arrays

Grouping with structs

Comparable data types

Collatable data types

Array type

NULLs and the array type

Declaring an array type

Constructing an array

Using array literals

Using generated values

Generating arrays of integers

Generating arrays of dates

Boolean type

Bytes type

Date type

Canonical format

Datetime type

Canonical format

Enum type

Geography type

Graph element type

Interval type

Canonical format

Constructing an interval

Construct an interval with a single datetime part

Construct an interval with a datetime part range

Interval-supported date and time parts

JSON type

Numeric types

Integer types

Decimal types

Floating point types

Floating point semantics

Mathematical function examples

Comparison operator examples

Protocol buffer type

Constructing a protocol buffer

NEW protocol_buffer {...} {: #using_new_map_constructor }

NEW protocol_buffer (...)

SELECT AS typename

Limited comparisons for protocol buffer values

Range type

Declare a range type

Construct a range

Construct a range with a constructor

Construct a range with a literal

Additional details

String type

Struct type

Declaring a struct type

Constructing a struct

Tuple syntax

Typeless struct syntax

Typed struct syntax

Limited comparisons for structs

Time type

Canonical format

Timestamp type

Canonical format

Time zones

Specify Coordinated Universal Time (UTC)

Specify an offset from Coordinated Universal Time (UTC)

Time zone name {: #time_zone_name}

Leap seconds

Ordering `NULL`s

`NULL`s and the array type

`NEW protocol_buffer {...}` {: #using_new_map_constructor }

`NEW protocol_buffer (...)`

`SELECT AS typename`