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 aTseparator[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.
Finally, a unified workspace for your SQL development
Get more done, together, with PopSQL and BigQuery
Previous