Trajectory Data Warehouses - Data Warehouse Systems: Design and Implementation - page 476

It is worth noting that temporal support has been introduced in the
SQL:2011 standard and has been implemented in DB2, Oracle, and Teradata.
However, such functionality adds temporality to tables, thus associating a
period to each row. However, to cope with the needs of trajectory data
warehousing, we need an alternative approach which adds temporality to
attributes, thus associating a period to an attribute value.
We have seen that, conceptually, a temporal type is a function from the
time domain to a base or spatial type. Thus, for each data type D ,where
D is a base type (e.g., INTEGER , REAL ,or BOOLEAN ) or a spatial type
(e.g., GEOMETRY or its subtypes), there are associated temporal types
T D(P,Q) ,where P is either PERIOD or INSTANT and Q is either DATE ,
TIME ,or TIMESTAMP .Inotherwords, P states whether the values are
recorded by intervals or by instants, whereas Q represents the granularity
at which data are represented in the period or instant P . For example, a type
T INTEGER(PERIOD,DATE) can be used to represent the evolution of the
salaries of employees shown in Fig. 12.1 . On the other hand, a value of a type
T FLOAT(INSTANT,TIME) can represent, for example, that the temperature
was 15.5 ◦ C at 8:00 a.m. and was 17 ◦ C at 9:00 a.m. In this case, we can use
(linear) interpolation functions to compute the value of temperature at any
time that is not explicitly recorded. As we have said, the time dimension is
assumed to represent valid time .
Temporal types are partial functions that may be undefined for certain
periods of time. We use the value NULL as undefined value. For example, in
Fig. 12.1 , the salary of John is NULL between 2012-07-01 and 2012-09-30.
Consider, for example, the following table definition:
CREATE TABLE Employees (
SSN INTEGER PRIMARY KEY,
FirstName VARCHAR(30),
LastName VARCHAR(30),
BirthDate DATE,
SalaryHist T INTEGER(PERIOD,DATE) )
A tuple can be inserted in this table as follows:
INSERT INTO Employee VALUES ( 123456789, ' John ' , ' Smith ' , ' 1980-01-01 ' ,
T INTEGER( 20 PERIOD( ' 2012-01-01 ' , ' 2012-07-01 ' ),
30 PERIOD( ' 2012-10-01 ' , ' 2013-01-01 ' )))
The value of SalaryHist above corresponds to the uppermost value shown in
Fig. 12.1 . For defining values of temporal types, we use the PERIOD data
type defined in the temporal extension of PostgreSQL mentioned above. The
periods above define closed-open intervals. Thus, for instance, the value 20
covers the period starting on 2012-01-01 up until the day before 2012-07-01.
Note that instead of a continuous function, we use two temporal attributes,
FromDate and ToDate , to indicate the validity interval of each tuple. Note
also that we use closed-open intervals.