Stay organized with collections
Save and categorize content based on your preferences.
Legacy SQL data types
This document details the data types supported by BigQuery's
legacy SQL query syntax. The preferred query syntax for BigQuery
is GoogleSQL. For information on data types in GoogleSQL, see
the GoogleSQL 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.
Imported BYTES data must be base64-encoded, except for Avro BYTES data,
which BigQuery can read and convert.
BYTES data read from a BigQuery table are base64-encoded,
unless you export to Avro format, in which case the Avro bytes
data type applies.
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.
CSV format:1 or 0, true or false, t or f, yes or no, or y or n (all case-insensitive).
JSON format:true or false (case-insensitive).
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 YYYY-MM-DD HH:MM:SS. The UTC and Z specifiers are supported.
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.
Legacy SQL has limited support for DATETIME. For more information,
see Civil time in legacy SQL.
Exact numeric in legacy SQL
You can read NUMERIC or BIGNUMERIC values in non-modifying clauses such as
SELECT list (with aliases), GROUP BY keys, and pass-through fields in
window functions, and so on. However, any computation over NUMERIC or
BIGNUMERIC values, including comparisons, produces undefined results.
The following cast and conversion functions are supported in legacy SQL:
CAST(<numeric> AS STRING)
CAST(<bignumeric> AS STRING)
CAST(<string> AS NUMERIC)
CAST(<string> AS BIGNUMERIC)
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
window 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
GoogleSQL, which supports all SQL
operations on the
DATE,
DATETIME, and
TIME data types.
[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-08-29 UTC."],[[["\u003cp\u003eLegacy SQL supports a variety of data types, including STRING, BYTES, INTEGER, FLOAT, NUMERIC, BIGNUMERIC, BOOLEAN, RECORD, TIMESTAMP, DATE, TIME, and DATETIME.\u003c/p\u003e\n"],["\u003cp\u003eLegacy SQL has limited support for NUMERIC, BIGNUMERIC, DATE, TIME, and DATETIME data types, restricting their use to non-modifying operations and specific casting functions.\u003c/p\u003e\n"],["\u003cp\u003eFor data types such as BYTES, BigQuery reads and converts Avro BYTES data, and when reading from BigQuery tables it are base64-encoded unless exported to Avro format.\u003c/p\u003e\n"],["\u003cp\u003eTIMESTAMP data can be represented as either UNIX timestamps or date and time strings, with BigQuery internally storing them as UNIX timestamps with microsecond precision.\u003c/p\u003e\n"],["\u003cp\u003eWhile legacy SQL provides these types, GoogleSQL is the preferred query syntax and offers full support for all SQL operations on DATE, DATETIME, and TIME data types.\u003c/p\u003e\n"]]],[],null,["# Legacy SQL data types\n=====================\n\n\nThis document details the data types supported by BigQuery's\nlegacy SQL query syntax. The preferred query syntax for BigQuery\nis GoogleSQL. For information on data types in GoogleSQL, see\nthe [GoogleSQL data types](/bigquery/docs/reference/standard-sql/data-types).\n\nLegacy SQL data types\n---------------------\n\nYour data can include the following data types:\n\nExact numeric in legacy SQL\n---------------------------\n\nYou can read NUMERIC or BIGNUMERIC values in non-modifying clauses such as\n`SELECT list (with aliases)`, `GROUP BY keys`, and pass-through fields in\nwindow functions, and so on. However, any computation over NUMERIC or\nBIGNUMERIC values, including comparisons, produces undefined results.\n\nThe following cast and conversion functions are supported in legacy SQL:\n\n- `CAST(\u003cnumeric\u003e AS STRING)`\n- `CAST(\u003cbignumeric\u003e AS STRING)`\n- `CAST(\u003cstring\u003e AS NUMERIC)`\n- `CAST(\u003cstring\u003e AS BIGNUMERIC)`\n\nCivil time in legacy SQL\n------------------------\n\nYou can read civil time data types---DATE, TIME, and\nDATETIME---and process them with non-modifying operators such as\n`SELECT list (with aliases)`, `GROUP BY keys`, and pass-through fields in\nwindow functions, etc. However, any other computation over civil time values,\nincluding comparisons, produces undefined results.\n\nThe following casts and conversion functions are supported in legacy SQL:\n\n- `CAST(\u003cdate\u003e AS STRING)`\n- `CAST(\u003ctime\u003e AS STRING)`\n- `CAST(\u003cdatetime\u003e AS STRING)`\n- `CAST(\u003cstring\u003e AS DATE)`\n- `CAST(\u003cstring\u003e AS TIME)`\n- `CAST(\u003cstring\u003e AS DATETIME)`\n\nIn practice, legacy SQL interprets civil time values as integers, and operations\non integers that you think are civil time values produce unexpected results.\n\nTo compute values using civil time data types, consider\n[GoogleSQL](/bigquery/sql-reference), which supports all SQL\noperations on the\n[DATE](/bigquery/sql-reference/data-types#date-type),\n[DATETIME](/bigquery/sql-reference/data-types#datetime-type), and\n[TIME](/bigquery/sql-reference/data-types#time-type) data types.\n\nWhat's next\n-----------\n\n- To set a field's data type using the API, see [`schema.fields.type`](/bigquery/docs/reference/rest/v2/tables#TableFieldSchema.FIELDS.type).\n- For GoogleSQL data types, see [data types](/bigquery/sql-reference/data-types)."]]