GoogleSQL data types

Choosing TIMESTAMP vs DATETIME, or NUMERIC vs FLOAT64, decides whether results are correct.

GoogleSQL has a compact set of types:

INT64 is the only integer type; INT, BIGINT and similar names are aliases for it.
FLOAT64 for approximate numbers; NUMERIC (38 digits of precision, 9 after the decimal point) and BIGNUMERIC for exact decimals such as money.
STRING (UTF-8, no length needed) and BYTES. BOOL.
DATE, TIME, DATETIME and TIMESTAMP. A TIMESTAMP is an absolute instant stored in UTC and displayed in UTC unless you format it with a time zone. A DATETIME is a calendar date and clock time with no time zone. Use TIMESTAMP for events; DATETIME only for "local wall-clock" values.
JSON for semi-structured documents, GEOGRAPHY for spatial data, INTERVAL and RANGE.
ARRAY<T> and STRUCT<...>, which let a row contain lists and nested records, covered in the nested data module.

Conversions use CAST(x AS INT64). A failed cast raises an error and aborts the query; SAFE_CAST returns NULL instead, which is the safe choice for messy input. Parsing functions like PARSE_DATE('%d/%m/%Y', s) and PARSE_TIMESTAMP take format strings, and most functions have a SAFE. prefixed variant that returns NULL on error, for example SAFE.PARSE_DATE(...).

Comparing a DATE with a TIMESTAMP needs a conversion: DATE(ts), DATE(ts, 'Europe/Berlin') for local days, or TIMESTAMP(d).

Example

SELECT
  CAST('42' AS INT64)                                  AS n,          -- 42
  SAFE_CAST('4x2' AS INT64)                            AS bad,        -- NULL
  CAST('19.99' AS NUMERIC) * 3                         AS exact,      -- 59.97
  TIMESTAMP '2025-06-01 22:30:00+00'                   AS ts,         -- 2025-06-01 22:30:00 UTC
  DATE(TIMESTAMP '2025-06-01 22:30:00+00', 'Europe/Berlin') AS local_day, -- 2025-06-02
  DATETIME '2025-06-01 22:30:00'                       AS wall_clock, -- no time zone
  SAFE.PARSE_DATE('%d/%m/%Y', '31/02/2025')            AS no_such_day,-- NULL
  JSON_VALUE(JSON '{"a": {"b": "x"}}', '$.a.b')        AS from_json;  -- x

Remember

INT64 is the one integer type; NUMERIC for exact decimals
TIMESTAMP = absolute instant (UTC); DATETIME = no time zone
SAFE_CAST and the SAFE. prefix return NULL instead of failing

Watch out for

Storing event times as DATETIME and losing which time zone they were in.

Check yourself

An event happened at 23:30 UTC. Which expression gives its calendar date in Tokyo?
What does SAFE_CAST('abc' AS INT64) return?

Official docs: docs.cloud.google.com/bigquery/docs/reference/standard-sql/data-types

Example

Remember

Watch out for

Check yourself

Builds on

Unlocks