This document details the data types supported by BigQuery's legacy SQL query syntax. The preferred query syntax for BigQuery is standard SQL. For information on data types in standard SQL, see the Standard SQL data types.
Legacy SQL data types
Your data can include the following data types:
Data type | Possible values | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
STRING | Variable-length character (UTF-8) data. | ||||||||||||
BYTES |
Variable-length binary data.
|
||||||||||||
INTEGER |
64-bit signed integer. If you are using the BigQuery API to load an integer outside the range of [-253+1, 253-1] (in most cases, this means larger than 9,007,199,254,740,991), into an integer (INT64) column, you must pass it as a string to avoid data corruption. This issue is caused by a limitation on integer size in JSON/ECMAScript. For more information, see the Numbers section of RFC 7159. |
||||||||||||
FLOAT | Double-precision floating-point format. | ||||||||||||
NUMERIC | Legacy SQL has limited support for NUMERIC. For more information, see NUMERIC in legacy SQL. | ||||||||||||
BOOLEAN |
|
||||||||||||
RECORD | A collection of one or more other fields. | ||||||||||||
TIMESTAMP |
You can describe TIMESTAMP data types as either UNIX timestamps or calendar datetimes. BigQuery stores TIMESTAMP data internally as a UNIX timestamp with microsecond precision. UNIX timestamps A positive or negative decimal number. A positive number specifies the number of seconds since the epoch (1970-01-01 00:00:00 UTC), and a negative number specifies the number of seconds before the epoch. Up to 6 decimal places (microsecond precision) are preserved. Date and time strings A date and time string in the format You can supply a timezone offset in your date and time strings, but BigQuery doesn't preserve the offset after converting the value to its internal format. If you need to preserve the original timezone data, store the timezone offset in a separate column. The leading zero is required when you specify a single-digit timezone offset. Date and time strings must be quoted when using JSON format. Examples The following examples show identical ways of describing specific dates, in both UNIX timestamp and date and time string formats.
|
||||||||||||
DATE | Legacy SQL has limited support for DATE. For more information, see Civil time in legacy SQL. | ||||||||||||
TIME | Legacy SQL has limited support for TIME. For more information, see Civil time in legacy SQL. | ||||||||||||
DATETIME | Legacy SQL has limited support for DATETIME. For more information, see Civil time in legacy SQL. |
NUMERIC in legacy SQL
You can read NUMERIC values and process them with non-modifying operators such
as SELECT list (with aliases)
, GROUP BY keys
, and pass-through fields in
analytic functions, and so on. However, any other computation over NUMERIC values,
including comparisons, produces undefined results.
The following cast and conversion functions are supported in legacy SQL:
CAST(<numeric> AS STRING)
CAST(<string> AS NUMERIC)
Civil time in legacy SQL
You can read civil time data types—DATE, TIME, and
DATETIME—and process them with non-modifying operators such as
SELECT list (with aliases)
, GROUP BY keys
, and pass-through fields in
analytic functions, etc. However, any other computation over civil time values,
including comparisons, produces undefined results.
The following casts and conversion functions are supported in legacy SQL:
CAST(<date> AS STRING)
CAST(<time> AS STRING)
CAST(<datetime> AS STRING)
CAST(<string> AS DATE)
CAST(<string> AS TIME)
CAST(<string> AS DATETIME)
In practice, legacy SQL interprets civil time values as integers, and operations on integers that you think are civil time values produce unexpected results.
To compute values using civil time data types, consider standard SQL, which supports all SQL operations on the DATE, DATETIME, and TIME data types.
What's next
- To set a field's data type using the API, see
schema.fields.type
. - For standard SQL data types, see data types.