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 theGoogleSQL data types.

Legacy SQL data types

Your data can include the following data types:

Data typePossible values
STRINGVariable-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, seethe Numbers section of RFC 7159.

FLOATDouble-precision floating-point format.
NUMERICLegacy SQL has limited support for NUMERIC. For more information, seeExact numeric in legacy SQL.
BIGNUMERICLegacy SQL has limited support for BIGNUMERIC. For more information, seeExact numeric in legacy SQL.
BOOLEAN
  • CSV format:1 or0,true orfalse,t orf,yes orno, ory orn (all case-insensitive).
  • JSON format:true orfalse (case-insensitive).
RECORDA 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 formatYYYY-MM-DD HH:MM:SS. TheUTC andZ 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.

EventUNIX timestamp formatDate/time string format
Minor (M4.2) earthquake near Oklahoma City
1408452095.2201408452095.220000
2014-08-19 07:41:35.220 -05:002014-08-19 12:41:35.220 UTC2014-08-19 12:41:35.2202014-08-19 12:41:35.2200002014-08-19T12:41:35.220Z
Neil Armstrong sets foot on the moon
-14182916
1969-07-20 20:18:041969-07-20 20:18:04 UTC1969-07-20T20:18:04
Deadline for fixingY10k bug
2534023008002.53402300800e11
10000-01-01 00:00
DATELegacy SQL has limited support for DATE. For more information, seeCivil time in legacy SQL.
TIMELegacy SQL has limited support for TIME. For more information, seeCivil time in legacy SQL.
DATETIMELegacy SQL has limited support for DATETIME. For more information, seeCivil time in legacy SQL.

Exact numeric in legacy SQL

You can read NUMERIC or BIGNUMERIC values in non-modifying clauses such asSELECT list (with aliases),GROUP BY keys, and pass-through fields inwindow functions, and so on. However, any computation over NUMERIC orBIGNUMERIC 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, andDATETIME—and process them with non-modifying operators such asSELECT list (with aliases),GROUP BY keys, and pass-through fields inwindow 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 operationson integers that you think are civil time values produce unexpected results.

To compute values using civil time data types, considerGoogleSQL, which supports all SQLoperations on theDATE,DATETIME, andTIME data types.

What's next

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-15 UTC.