How to Use SQL:2011 Times and Periods
Although versions of the SQL standard prior to SQL:2011 provided for DATE, TIME, TIMESTAMP, and INTERVAL data types, they did not address the idea of a period of time with a definite start time and a definite end time.
One way of addressing this need is to define a new PERIOD data type. SQL:2011 however, does not do this. To introduce a new data type into SQL at this late stage in its development would wreak havoc with the ecosystem that has built up around SQL. Major surgery to virtually all existing database products would be required to add a new data type.
SQL:2011 solves the problem by adding period definitions as metadata to tables. A period definition is a named table component, identifying a pair of columns that capture the period start and the period end time. The CREATE TABLE and ALTER TABLE statements used to create and modify tables have been updated with new syntax to create or destroy the periods created by these new period definitions.
A PERIOD is determined by two columns: a start column and an end column. These columns are conventional, just like the columns of the existing date data types, each with its own unique name. As mentioned previously, a period definition is a named table component. It occupies the same name space as column names, so it must not duplicate any existing column name.
SQL follows a closed-open model for periods, meaning that a period includes the start time but not the end time. For any table row, a period end time must be greater than its start time. This is a constraint that is enforced by the DBMS.
There are two dimensions of time that are important when dealing with temporal data:
Valid time is the time period during which a row in a table correctly reflects reality.
Transaction time is the time period during which a row is committed to or recorded in a database.
The valid time and the transaction time for a row in a table need not be the same. For example, in a business database that records the period during which a contract is in effect, the information about the contract may be (and probably is) inserted before the contract start time.
In SQL:2011, separate tables may be created and maintained to accommodate the two different kinds of time, or a single, bitemporal table may serve the purpose. Transaction time information is kept in system-versioned tables, which contain the system-time period, denoted by the keyword SYSTEM_TIME.
Valid time information, on the other hand, is maintained in tables that contain an application-time period. You can give an application-time period any name you want, provided the name is not already used for something else. You’re allowed to define at most one system-time period and one application-time period.
Although temporal data support in SQL is being introduced for the first time in SQL:2011, people have had to deal with temporal data long before the temporal constructs of SQL:2011 were included in any database products. This was typically done by defining two table columns, one for the start datetime and the other for the end datetime.
The fact that SQL:2011 does not define a new PERIOD data type, but rather uses period definitions as metadata, means that existing tables with such start and end columns can easily be upgraded to incorporate the new capability. The logic for providing period information can be removed from existing application programs, simplifying them, speeding them up, and making them more reliable.