PopSQL

Date and Time Data Types in BigQuery

BigQuery supports 4 main date and 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

Be sure to check out our Date and Time Function cheat sheet to see how querying date and times differs in BigQuery and across databases.

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