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
From BigQuery query to chart to Slack in seconds
Get to answers faster, together, with PopSQL and BigQuery