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

A tri-temporal database has three axes of time:

Valid time
Transaction time
Decision time

This approach introduces additional complexities.

Temporal databases are in contrast to current databases (not to be confused with currently available 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: In late 1993, Snodgrass presented this work to the group responsible for the American National Standard for Database Language SQL, ANSI Technical Committee X3H2 (now known as NCITS H2). 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 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" (valid time tables), "system-versioned tables" (transaction time tables) and "system-versioned application-time period tables" (bitemporal 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 (DS) or date-timestamps (DTS) can be bound together using a <code>PERIOD FOR</code> declaration. Another difference is replacement of the controversial (prefix) statement modifiers from TSQL2 with a set of temporal predicates.

Decision time periods can only occur in the past or up to the transaction time. As in a transaction time table, records are never deleted. Only new records can be inserted, and existing ones updated by setting their decision end time to show that they are no longer current.

To enable decision time, two more fields are added to a database table: <code>decision_from</code> and <code>decision_to</code>. Here, <code>decision_from</code> is the time a decision was made, and <code>decision_to</code> is the time that the decision was superseded (which may be infinity if it has not yet been superseded). When combined with transaction time, this makes the table into a tritemporal table.

The following is a list of real events that occurred between the 1964 and 1976 United States presidential elections:

{| class="wikitable"

!Date

!Decision maker

!Real world event

|1964-11-03

|Electoral College

|Election of 1964

|1968-11-05

|Electoral College

|Election of 1968

|1972-11-07

|Electoral College

|Election of 1972

|1973-10-10

|Spiro Agnew

|Agnew resigns

|1973-10-12

|Richard Nixon

|Nixon nominates Ford

|1973-12-06

|Congress

|Congress confirms Ford

|1974-08-09

|Richard Nixon

|Nixon resigns

|1974-08-20

|Gerald Ford

|Ford nominates Rockefeller

|1974-12-19

|Congress

|Congress confirms Rockefeller

|1976-11-02

|Electoral College

|Election of 1976

In this example, a constant 7-day delay is assumed between the decision time and the transaction time when the data is committed to the database. Given those conditions, the database would have contained the following information after the election in 1976:

{| class="wikitable"

| colspan="2" |

! colspan="2" |Valid

! colspan="2" |Decision

! colspan="2" |Transaction

!President

!Vice

!From

!To

!From

!To

!From

!To

|- style="border-bottom:double"

|Johnson

|Humphrey

|1965-01-20

| style="color:red" |1969-01-20

|1964-11-03

|∞

|1964-11-10

|∞

|- style="border-bottom:double"

|Nixon

|Agnew

| style="color:red" |1969-01-20

| style="color:orange" |1973-01-20

|1968-11-05

|∞

|1968-11-12

|∞

|- style="border-bottom:double"

|Nixon

|Agnew

| style="color:orange" |1973-01-20

|1977-01-20

|1972-11-07

|∞

|1972-11-14

| style="color:red" |1973-10-17

|Nixon

|Agnew

| style="color:orange" |1973-01-20

|1977-01-20

|1972-11-07

| style="color:red" |1973-10-10

| style="color:red" |1973-10-17

|∞

|Nixon

|Agnew

| style="color:orange" |1973-01-20

| style="color:lime" |1973-10-10

| style="color:red" |1973-10-10

|∞

|1973-10-17

|∞

|- style="border-bottom:double"

|Nixon

|(Vacant)

| style="color:lime" |1973-10-10

|1977-01-20

|1973-10-10

|∞

|1973-10-17

| style="color:orange" |1973-12-13

|- style="border-bottom:double"

|Nixon

|Ford

|∞

|1977-01-20

|1973-10-12

|∞

|1973-10-19

| style="color:lime" |1973-12-13

|Nixon

|(Vacant)

| style="color:lime" |1973-10-10

|1977-01-20

|1973-10-10

| style="color:orange" |1973-12-06

| style="color:orange" |1973-12-13

|∞

|Nixon

|(Vacant)

| style="color:lime" |1973-10-10

| style="color:green" |1973-12-06

| style="color:orange" |1973-12-06

|∞

|1973-12-13

|∞

|Nixon

|Ford

|∞

|1977-01-20

|1973-10-12

| style="color:lime" |1973-12-06

| style="color:lime" |1973-12-13

|∞

|- style="border-bottom:double"

|Nixon

|Ford

| style="color:green" |1973-12-06

|1977-01-20

| style="color:lime" |1973-12-06

|∞

|1973-12-13

| style="color:green" |1974-08-15

|Nixon

|Ford

| style="color:green" |1973-12-06

|1977-01-20

|1973-12-06

| style="color:green" |1974-08-08

| style="color:green" |1974-08-15

|∞

|Nixon

|Ford

| style="color:green" |1973-12-06

| style="color:blue" |1974-08-09

| style="color:green" |1974-10-08

|∞

|1974-08-15

|∞

|- style="border-bottom:double"

|Ford

|(Vacant)

| style="color:blue" |1974-08-09

|1977-01-20

|1974-10-08

|∞

|1974-08-15

| style="color:blue" |1974-12-26

|- style="border-bottom:double"

|Ford

|Rockefeller

|∞

|1977-01-20

|1974-10-20

|∞

|1974-08-27

| style="color:purple" |1974-12-26

|Ford

|(Vacant)

| style="color:blue" |1974-08-09

|1977-01-20

|1974-10-08

| style="color:blue" |1974-12-19

| style="color:blue" |1974-12-26

|∞

|Ford

|(Vacant)

| style="color:blue" |1974-08-09

| style="color:purple" |1974-12-19

| style="color:blue" |1974-12-19

|∞

|1974-12-26

|∞

|Ford

|Rockefeller

|∞

|1977-01-20

|1974-08-20

| style="color:purple" |1974-12-19

| style="color:purple" |1974-12-26

|∞

|- style="border-bottom:double"

|Ford

|Rockefeller

| style="color:purple" |1974-12-19

|1977-01-20

| style="color:purple" |1974-12-19

|∞

|1974-12-26

|∞

|Carter

|Mondale

|1977-01-20

|1981-01-20

|1976-11-02

|∞

|1976-11-09

|∞

Given the 7-day delayed table above, the question "who was president and vice president for the valid time of 1977-01-01" (which given the 7-day delay could provide data for 1976-12-25) would be:

Nixon/Agnew when using a decision time and transaction time of 1972-11-14
Nixon/(Vacant) when using a decision time and transaction time of 1973-10-17
Nixon/Ford when using a decision time and transaction time of 1974-08-08
Ford/(Vacant) when using a decision time of 1974-08-08 and transaction time of current
Ford/Rockefeller when using a decision time and transaction time of current

Bitemporal modelling

A bitemporal model contains both valid and transaction time. This provides both historical and rollback information. Historical information (e.g.: "Where did John live in 1992?") is provided by the valid time. Rollback (e.g.: "In 1992, where did the database believe John lived?") is provided by the transaction time. The answers to these example questions may not be the same the database may have been altered since 1992, causing the queries to produce different results.

The valid time and transaction time do not have to be the same for a single fact. For example, consider a temporal database storing data about the 18th century. The valid time of these facts is somewhere between 1701 and 1800. The transaction time would show when the facts were inserted into the database (for example 1998-01-21).

Schema evolution

A challenging issue is the support of temporal queries in a transaction time database under evolving schema. In order to achieve perfect archival quality it is of key importance to store the data under the schema version under which they first appeared. However, even the most simple temporal query rewriting the history of an attribute value would be required to be manually rewritten under each of the schema versions, potentially hundreds as in the case of MediaWiki. This process would be particularly taxing for users. A proposed solution is to provide automatic query rewriting, although this is not part of SQL or similar standards.

Approaches to minimize the complexities of schema evolution are to:

Use a semi-structured database/NoSQL database which reduces the complexities of modeling attribute data but provides no features for handling multiple time axes.
Use a database capable of storing both semi-structured data for attributes and structured data for time axes (e.g., SnowflakeDB, PostgreSQL)

Implementations in notable products

The following implementations provide temporal features in a relational database management system (RDBMS).

MariaDB version 10.3.4 added support for SQL:2011 standard as "System-Versioned Tables".
Oracle Database Oracle Workspace Manager is a feature of Oracle Database which enables application developers and DBAs to manage current, proposed and historical versions of data in the same database.
PostgreSQL version 9.2 added native ranged data types that are capable of implementing all of the features of the pgFoundry temporal contributed extension. The PostgreSQL range types are supported by numerous native operators and functions.
Teradata provides two products. Teradata version 13.10 and Teradata version 14 have temporal features based on TSQL2 built into the database.
IBM Db2 version 10 added a feature called "time travel query" which is based on the temporal capabilities of the SQL:2011 standard.
Microsoft SQL Server introduced Temporal Tables as a feature for SQL Server 2016. The feature is described in a video on Microsoft's "Channel 9" web site.

Non-relational, NoSQL database management systems that provide temporal features including the following:

TerminusDB is a fully featured open source graph database that natively supports version control, time-travel queries and diffing functions. It has an immutable layer architecture based on delta encoding and succinct data structures.
MarkLogic introduced bitemporal data support in version 8.0. Time stamps for Valid and System time are stored in JSON or XML documents.

Temporal databases were one of the earliest forms of data version control, and influenced the development of modern data versioning systems.

Alternatives

thumb|Example of [[slowly changing dimension (SCD) model]]

Slowly changing dimensions can be used to model temporal relations.

References

External links

Temporal Relations in RDF
Temporal Scope for RDF Triples
IBM DB2 10 for z/OS
Time and Time Again series of articles by Randy Weis and Tom Johnston
Temporal Patterns by Martin Fowler

Temporal database

Types

Uni-temporal

Bi-temporal

Tri-temporal

Features

Bitemporal modelling

Schema evolution

Implementations in notable products

Alternatives

Further reading

See also

References

External links