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

NameDescriptionStorage SizeNote
INT64 (Integer)A whole number that is not a fraction.8 bytesRange from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
NUMERICA number with 38 digits of precision, 9 of which can be after the decimal point.16 bytesRange from -99999999999999999999999999999.999999999 to 99999999999999999999999999999.999999999
FLOAT64 (Float)Double precision (approximate) decimal values.8 bytesWikipedia will take this one.
BOOL (Boolean)Represented by keywords TRUE and FALSECase insensitive so TRUE or false will work.
STRINGVariable-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.
BYTESVariable-length binary data.Not to be used interchangeably with STRING.
BYTES operates on raw bytes rather than Unicode characters.
ARRAYOrdered list of zero or more elements of any non-ARRAY type.

Time Data Types

NameDescriptionCanonical FormatNote
DATERepresents a logical calendar date, without time.YYYY-[M]M-[D]DBYTES operates on raw bytes rather than Unicode characters. Range from 0001-01-01 to 9999-12-31
TIMERepresents a time, independent of a specific date.[H]H:[M]M:[S]S[.DDDDDD]Range from 00undefined00 to 23undefined59.999999
DATETIMERepresents 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 00undefined00 to 9999-12-31 23undefined59.999999
TIMESTAMPRepresents 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 00undefined00 to 9999-12-31 23undefined59.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

NameDescriptionNote
STRUCTA container of ordered fields, each with a type (required) and field name (optional).Learn more about STRUCT
GEOGRAPHYA 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)
database icon
Real-time SQL collaboration is here
Get started with PopSQL and BigQuery in minutes