PopSQL

Date and Time Data Types in BigQuery

BigQuery supports 4 main date and time data types:

NameDescriptionCanonical FormatNote
DATERepresents a logical calendar date, without time.YYYY-[M]M-[D]DRange 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

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.

database icon
From BigQuery query to chart to Slack in seconds
Get to answers faster, together, with PopSQL and BigQuery