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 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. 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.
FLOAT	Double-precision floating-point format.
NUMERIC	Legacy SQL has limited support for NUMERIC. For more information, see NUMERIC in legacy SQL.
BOOLEAN	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. Event UNIX timestamp format Date/time string format Minor (M4.2) earthquake near Oklahoma City 1408452095.220 1408452095.220000 2014-08-19 07:41:35.220 -05:00 2014-08-19 12:41:35.220 UTC 2014-08-19 12:41:35.220 2014-08-19 12:41:35.220000 2014-08-19T12:41:35.220Z Neil Armstrong sets foot on the moon -14182916 1969-07-20 20:18:04 1969-07-20 20:18:04 UTC 1969-07-20T20:18:04 Deadline for fixing Y10k bug 253402300800 2.53402300800e11 10000-01-01 00:00
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.