Data Types in BigQuery

BigQuery supports all common data types found in Standard SQL. Google Cloud has verbose documentation, but here it is presented short and sweet:

Most Common

Name Description Storage Size Note
INT64
(Integer)
A whole number that is not a fraction. 8 bytes Range from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
NUMERIC A number with 38 digits of precision, 9 of which can be after the decimal point. 16 bytes Range from -99999999999999999999999999999.999999999 to 99999999999999999999999999999.999999999
FLOAT64
(Float)
Double precision (approximate) decimal values. 8 bytes Wikipedia will take this one.
BOOL
(Boolean)
Represented by keywords TRUE and FALSE Case insensitive so TRUE or false will work.
STRING Variable-length character (Unicode) data. Must be quoted with either single (') or double (") quotation marks. Alternatively, can be triple-quoted with groups of three single (''') or three double (""") quotation marks.
BYTES Variable-length binary data. Not to be used interchangeably with STRING.
BYTES operates on raw bytes rather than Unicode characters.
ARRAY Ordered list of zero or more elements of any non-ARRAY type.

Time Data Types

Name Description Canonical Format Note
DATE Represents a logical calendar date, without time. YYYY-[M]M-[D]D Range from 0001-01-01 to 9999-12-31
TIME Represents a time, independent of a specific date. [H]H:[M]M:[S]S[.DDDDDD] Range from 00:00:00 to 23:59:59.999999
DATETIME Represents a year, month, day, hour, minute, second, and subsecond without a timezone. † YYYY-[M]M-[D]D[(|T)[H]H:[M]M:[S]S[.DDDDDD]] Range from 0001-01-01 00:00:00 to 9999-12-31 23:59:59.999999
TIMESTAMP Represents an absolute point in time, with microsecond precision with a timezone. YYYY-[M]M-[D]D[( |T)[H]H:[M]M:[S]S[.DDDDDD]][time zone] Range from 0001-01-01 00:00:00 to 9999-12-31 23:59:59.999999

DATETIME is seldom used, as it's rare to wish to omit the timezone.

How to read Canonical Format:

  • YYYY: Four-digit year
  • [M]M: One or two digit month
  • [D]D: One or two digit day
  • ( |T): A space or a T separator
  • [H]H: One or two digit hour (valid values from 00 to 23)
  • [M]M: One or two digit minutes (valid values from 00 to 59)
  • [S]S: One or two digit seconds (valid values from 00 to 59)
  • [.DDDDDD]: Up to six fractional digits (microsecond precision)
  • [time zone]: String representing the time zone, with two canonical formats:
    • Time zone name per the tz database
    • Offset from Coordinated Universal Time (UTC), or the letter Z for UTC

Less Common

Name Description Note
STRUCT A container of ordered fields, each with a type (required) and field name (optional). Learn more about STRUCT
GEOGRAPHY A collection of points, lines, and polygons, which is represented as a point set, or a subset of the surface of the Earth. Based on the Open Geospatial Consortium’s (OGC) Simple Features specification (SFS)

Cta
Better SQL for the people
Get more done with PopSQL and BigQuery