ISO IEC 19075-2 pdf – Information technology — Guidance for the use of database language SQL — Part 2: Time-related information
4.6Periods
A period is an object associated with a single base table.A period definition for a given table associatesa period name with a pair of column names defined for that table.lt is required that the columns are bothof a datetime data type and known not nullable.Furthermore, it is required that the declared types ofboth columns are identical.
Similar to column definitions and constraint definitions, a period definition can only be specified as partof a table definition.For a table with a period definition, every row in that table is considered to be
associated with a period whose name corresponds to the period name specified in the period definitionand whose start and end times are provided by the column values specified in the period definition.For example, consider the table definition:
CREATETABLE emp
(emp_id INTEGER NOT NULL,name VARCHAR(30 ) ,
salary DECIMAL{5,2),dept_id INTEGER,
bus_start DATE NOT NULL,bus_end DATE NOT NULL,
PERIOD FOR business_time (bus_start, bus_end)) ;
The period definition”PERIOD FORbusiness_time (bus_start, bus_end)” in the preceding table definitiondefines a period named business_time for the emp table with the value in the bus_ start column for agivenrow acting as the start time of the business_time period associated with the row and the value in thebus_end column for a given row acting as the end time of the business_time period associated with therow.
ln general, for a period with name P, the first column in the period definition is called the P period startcolumn, and the second column is called the P period end column.The columns participating in a perioddefinition shall satisfy the conditions:
1)Both columns shall be declared as NOT NULL.
2) The data type of both columns shall be a datetime data type.3) The data types of both columns shall be identical.
For any given period, the SQL-implementation ensures the value of the period end column is alwaysgreater than the value of the period start column.In general, the period is a set of datetime values con-sisting of every distinct value in the timeline starting from the period start value up to but not includingthe period end value.
Agiven table can have at most two period definitions.One of the periods is reserved for supporting system-time dimension, i.e., system-versionedtables with a predefined period name of “SYSTEM_TIME” for sucha period.This leaves one period for supporting the application-time dimension with a user-defined namefor such a period.
4.7 Operations involving datetimes and intervals Table 9, “Valid operators involving datetimes and intervals”, specifies the declared types of arithmetic expressions involving datetime and interval operands.
Arithmetic operations involving values of type datetime or interval obey the natural rules associatedwith dates and times and yield valid datetime or interval results according to the Gregorian calendar.Operations involving values of type datetime require that the datetime values be comparable.Operationsinvolving values of type interval require that the interval values be comparable.
Operations involving a datetime and an interval preserve the time zone of the datetime operand.If thedatetime operand does not include a time zone displacement, then the result has no time zone displace-ment.
An extract expression operates on a datetime orinterval and returnsan exact numeric value representingthe value of one component of the datetime or interval.
An interval absolute value function operates on an interval argument and returns its absolute value inthe same most specific type.
4.8Time-related predicates
4.8.1 overlaps predicate
An overlaps predicate uses the operator OVERLAPS to determine whether or not two chronologicalperiods overlap in time. A chronological period is specified either as a pair of datetimes (starting andending) or as a starting datetime and an interval.If the length of the period is greater than 0 (zero), thenthe period consists of all points of time greater than or equal to the lower endpoint, and less than theupper endpoint. If the length of the period is equal to 0(zero), then the period consists of a single pointin time, thelowerendpoint. Two chronologicalperiods overlap if they have atleast one point in common.