Temporal database
A temporal database stores data relating to time instances. It offers temporal data types and stores information relating to past, present and future time.
Temporal databases can be uni-temporal, bi-temporal or tri-temporal.
More specifically the temporal aspects usually include valid time, transaction time and/or decision time.
- Valid time is the time period during or event time at which a fact is true in the real world.
- Transaction time is the time at which a fact was recorded in the database.
- Decision time is the time at which the decision was made about the fact. Used to keep a history of decisions about valid times.
Types
Uni-temporal
A uni-temporal database has one axis of time, either the validity range or the system time range.Bi-temporal
A bi-temporal database has two axes of time:- Valid time
- Transaction time or decision time
Tri-temporal
- Valid time
- Transaction time
- Decision time
Temporal databases are in contrast to current databases, which store only facts which are believed to be true at the current time.
Features
Temporal databases support managing and accessing temporal data by providing one or more of the following features:- A time period datatype, including the ability to represent time periods with no end
- The ability to define valid and transaction time period attributes and bitemporal relations
- System-maintained transaction time
- Temporal primary keys, including non-overlapping period constraints
- Temporal constraints, including non-overlapping uniqueness and referential integrity
- Update and deletion of temporal records with automatic splitting and coalescing of time periods
- Temporal queries at current time, time points in the past or future, or over durations
- Predicates for querying time periods, often based on Allen's interval relations
History
Richard Snodgrass proposed in 1992 that temporal extensions to SQL be developed by the temporal database community. In response to this proposal, a committee was formed to design extensions to the 1992 edition of the SQL standard ; those extensions, known as TSQL2, were developed during 1993 by this committee. In late 1993, Snodgrass presented this work to the group responsible for the American National Standard for Database Language SQL, ANSI Technical Committee X3H2. The preliminary language specification appeared in the March 1994 ACM SIGMOD Record. Based on responses to that specification, changes were made to the language, and the definitive version of the TSQL2 Language Specification was published in September, 1994
An attempt was made to incorporate parts of TSQL2 into the new SQL standard SQL:1999, called SQL3. Parts of TSQL2 were included in a new substandard of SQL3, ISO/IEC 9075-7, called SQL/Temporal. The TSQL2 approach was heavily criticized by Chris Date and Hugh Darwen. The ISO project responsible for temporal support was canceled near the end of 2001.
As of December 2011, ISO/IEC 9075, Database Language SQL:2011 Part 2: SQL/Foundation included clauses in table definitions to define "application-time period tables", "system-versioned tables" and "system-versioned application-time period tables". A substantive difference between the TSQL2 proposal and what was adopted in SQL:2011 is that there are no hidden columns in the SQL:2011 treatment, nor does it have a new data type for intervals; instead two columns with datestamps or date-timestamps can be bound together using a
PERIOD FOR declaration. Another difference is replacement of the controversial statement modifiers from TSQL2 with a set of temporal predicates.Other features of SQL:2011 standard related to temporal databases are automatic time period splitting, temporal primary keys, temporal referential integrity, temporal predicates with Allen's interval algebra and time-sliced and sequenced queries.
Example
For illustration, consider the following short biography of a fictional man, John Doe:Using a non-temporal database
To store the life of John Doe in a current database we use a table.John's father officially reported his birth on 1975-04-04. On this date a Smallville official inserted the following entry in the database:
Person.Note that the date itself is not stored in the database.
After graduation, John moves out, but forgets to register his new address. John's entry in the database is not changed until 1994-12-27, when he finally reports it. A Bigtown official updates his address in the database. The
person table now contains Person. Note that the information of John living in Smallville has been overwritten, so it is no longer possible to retrieve that information from the database. An official accessing the database on 1994-12-28, would be told that John lives in Bigtown. More technically: if a database administrator ran the query on 1994-12-26, the result would be Smallville. Running the same query 2 days later would result in Bigtown.Until his death, the database would state that he lived in Bigtown. On 2001-04-01, the coroner deletes the John Doe entry from the database. After this, running the above query would return no result at all.
| Date | Real world event | Database action | What the database shows |
| John is born | There is no person called John Doe | ||
| John's father officially reports John's birth | Inserted:Person | John Doe lives in Smallville | |
| After graduation, John moves to Bigtown, but forgets to register his new address | John Doe lives in Smallville | ||
| Nothing | John Doe lives in Smallville | ||
| John registers his new address | Updated:Person | John Doe lives in Bigtown | |
| John dies | Deleted:Person | There is no person called John Doe |
Using a single axis: valid time or transaction time
is the time for which a fact is true in the real world. A valid time period may be in the past, span the current time, or occur in the future.For the example above, to record valid time, the
person table has two fields added, valid_from and valid_to. These specify the period when a person's address is valid in the real world. On 1975-04-04, John's father registered his son's birth. An official then inserts a new entry into the database stating that John lives in Smallville from April 3. Note that although the data was inserted on the fourth, the database states that the information is valid since the third. The official does not yet know if or when John will move to another place, so the valid_to field is set to infinity. The entry in the database is:On 1994-12-27, John reports his new address in Bigtown where he has been living since 1994-08-26. A new database entry is made to record this fact:
The original entry
Person is not deleted, but has the valid_to attribute updated to reflect that it is now known that John stopped living in Smallville on 1994-08-26. The database now contains two entries for John Doe:| Name | City | Valid from | Valid to |
| John Doe | Smallville | 1975-04-03 | 1994-08-26 |
| John Doe | Bigtown | 1994-08-26 | ∞ |
When John dies his current entry in the database is updated stating that John does not live in Bigtown any longer. The database now looks like this:
| Name | City | Valid from | Valid to |
| John Doe | Smallville | 1975-04-03 | 1994-08-26 |
| John Doe | Bigtown | 1994-08-26 | 2001-04-01 |
Using two axes: valid time and transaction time
records the time period during which a database entry is accepted as correct. This enables queries that show the state of the database at a given time. Transaction time periods can only occur in the past or up to the current time. In a transaction time table, records are never deleted. Only new records can be inserted, and existing ones updated by setting their transaction end time to show that they are no longer current.To enable transaction time in the example above, two more fields are added to the Person table:
transaction_from and transaction_to. Here, transaction_from is the time a transaction was made, and transaction_to is the time that the transaction was superseded. This makes the table into a [|bitemporal table].What happens if the person's address as stored in the database is incorrect? Suppose an official accidentally entered the wrong address or date? Or, suppose the person lied about their address for some reason. Upon discovery of the error, the officials update the database to correct the information recorded.
For example, from 1995-06-01 to 2000-09-03, John Doe moved to Beachy. But to avoid paying Beachy's exorbitant residence tax, he never reported it to the authorities. Later during a tax investigation, it is discovered on 2-Feb-2001 that he was in fact in Beachy during those dates. To record this fact, the existing entry about John living in Bigtown must be split into two separate records, and a new record inserted recording his residence in Beachy. The database would then appear as follows:
| Name | City | Valid from | Valid to |
| John Doe | Smallville | 1975-04-03 | 1994-08-26 |
| John Doe | Bigtown | 1994-08-26 | 1995-06-01 |
| John Doe | Beachy | 1995-06-01 | 2000-09-03 |
| John Doe | Bigtown | 2000-09-03 | 2001-04-01 |
However, this leaves no record that the database ever claimed that he lived in Bigtown during 1995-06-01 to 2000-09-03. This might be important to know for auditing reasons, or to use as evidence in the official's tax investigation. Transaction time allows capturing this changing knowledge in the database, since entries are never directly modified or deleted. Instead, each entry records when it was entered and when it was superseded. The database contents then look like this:
| Name | City | Valid from | Valid to | Entered | Superseded |
| John Doe | Smallville | 1975-04-03 | ∞ | 1975-04-04 | 1994-12-27 |
| John Doe | Smallville | 1975-04-03 | 1994-08-26 | 1994-12-27 | ∞ |
| John Doe | Bigtown | 1994-08-26 | ∞ | 1994-12-27 | 2001-02-02 |
| John Doe | Bigtown | 1994-08-26 | 1995-06-01 | 2001-02-02 | ∞ |
| John Doe | Beachy | 1995-06-01 | 2000-09-03 | 2001-02-02 | ∞ |
| John Doe | Bigtown | 2000-09-03 | ∞ | 2001-02-02 | 2001-04-01 |
| John Doe | Bigtown | 2000-09-03 | 2001-04-01 | 2001-04-01 | ∞ |
The database records not only what happened in the real world, but also what was officially recorded at different times.