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.|
Variable-length binary data.
|INTEGER||64-bit signed integer.|
|FLOAT||Double-precision floating-point format.|
|RECORD||A collection of one or more other fields.|
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.
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.
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.|
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.