ISO IEC 19075-2:2021 pdf download – Information technology — Guidance for the use of database language SQL — Part 2: Time-related information.
4 Time-related data types, constructs, operators, and predicates 4.1 Context of time-related specifications The requirements for the material discussed in this document shall be as specified in ISO/IEC 9075-1 and ISO/IEC 9075-2. 4.2 Datetime types ISO/IEC 9075-2 defines the time-related data types, constructs, operators, and predicates described in this document. ISO/IEC 9075-2 specifies requirements for the material discussed in this document. There are three datetime types, each of which is made up of different datetime fields. A value of data type TIMESTAMP is made up of the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It is always a valid time at a valid Gregorian date. A value of data type TIME comprises values of the datetime fields HOUR, MINUTE and SECOND. It is always a valid time of day. A value of data type DATE is made up of the datetime fields YEAR, MONTH, and DAY. It is always a valid Gregorian date. TIMESTAMP and TIME may be specified with a number of (decimal) digits of fractional seconds precision. TIMESTAMP and TIME may also be specified as being WITH TIME ZONE, in which case every value has associated with it a time zone displacement (the offset of the TIMESTAMP or TIME from Coördinated Universal Time, UTC). In comparing values of a data type WITH TIME ZONE, the value of the time zone displacement is disregarded. Table 1, “Fields in datetime values”, specifies the fields that can make up a datetime value.
On occasion, UTC is adjusted by the omission of a second or the insertion of a “leap second” in order to maintain synchronization with sidereal time. This implies that sometimes, but very rarely, a particular minute will contain exactly 59, 61, or 62 seconds. Interval arithmetic that involves leap seconds or dis- continuities in calendars will produce implementation-defined results. For the convenience of users, whenever a datetime value with time zone is to be implicitly derived from one without (for example, in a simple assignment operation), SQL assumes the value without time zone to be local, subtracts the current default time zone displacement of the SQL-session from it to give UTC, and associates that time zone displacement with the result. Conversely, whenever a datetime value without time zone is to be implicitly derived from one with, SQL assumes the value with time zone to be UTC, adds the time zone displacement to it to give local time, and the result, without any time zone displacement, is local. Datetime data types will allow dates in the Gregorian format to be stored in the date range 0001–01–01 CE through 9999–12–31 CE.
ISO IEC 19075-2:2021 pdf download – Information technology — Guidance for the use of database language SQL — Part 2: Time-related information
