Definition
A combined format of both date and time.
Definition
Value domains utilising the DateTime datatype may optionally combine it with a time of day based on a 24-hour clock. A decimal fraction may be added to the lowest order time element present, in any of these representations (to 6 places), as can time zone information using UTC offsets.
Date and time values are ordered from the largest to smallest unit of time: year, month, day, hour, minute, second, and fraction of second. All preceding elements must be used when expressing a specific value (excepting fractions), and the year is always required at a minimum, as is the hour if recording time.
DateTime adheres to the extended format of ISO 8601-1:2019 Data elements and interchange formats - Information interchange - Representation of dates and times, where the following representations and requirement obligations are used:
DateTime Notation Elements, Meanings and Obligations
| Element | Meaning | Obligation |
|---|---|---|
| YYYY | A four digit year | Required |
| MM | A zero-padded month between 01 and 12 | Optional |
| DD | A zero-padded day between 01 and 31 | Optional |
| hh | A zero-padded hour between 00 and 23 | Optional |
| mm | A zero-padded minute between 00 and 59 | Optional |
| ss | A zero-padded second between 00 and 59 | Optional |
| s[6] | Fractions of a second, with up to 6 digits of precision | Optional |
Characters used as designations and separators
| Element | Meaning | Obligation |
|---|---|---|
| T | Used as a time designator to indicate the start of the representation of the time of day in date and time of day elements | Conditional |
| Z | Used to indicate a time element is in Coordinated Universal Time (UTC) rather than a local time zone | Conditional |
| - | Hyphen - used to separate the time elements "year" and "month", "month" and "day", and "week" and "day" | Conditional |
| : | Colon - used to separate the time elements "hour" and "minute", and "minute" and "second" | Conditional |
| . | Decimal mark - used a separator between the time element and its fraction | Conditional |
| ± | Plus or minus sign - for time zones, represents a plus sign [+] if in combination with the following element a positive value or zero needs to be represented, or a minus sign [-] if in combination with the following element a negative value needs to be represented. | Conditional |
References
Google LLC. (2021, May 20). DATETIME - Data types in Standard SQL | Bigquery | Google Cloud. Retrieved May 21, 2021, from https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#datetime_type
International Organization for Standardization. (n.d.). ISO 8601-1:2019(en) Date and time — Representations for information interchange — Part 1: Basic rules. Retrieved May 21, 2021, from https://www.iso.org/obp/ui/#iso:std:iso:8601:-1:ed-1:v1:en
Related content
| Relation | Count |
|---|---|
| Value Domains implementing this Data Type | 3 |