Editing Temporal database (section)

==History==
With the development of [[SQL]] and its attendant use in real-life applications, database users realized that when they added date columns to key fields, some issues arose. For example, if a table has a primary key and some attributes, adding a date to the primary key to track historical changes can lead to creation of more rows than intended. Deletes must also be handled differently when rows are tracked in this way. In 1992, this issue was recognized but standard database theory was not yet up to resolving this issue, and neither was the then-newly formalized [[SQL-92|SQL-92 standard]].

[[Richard T. Snodgrass|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 (ANSI X3.135.-1992 and ISO/IEC 9075:1992); those extensions, known as TSQL2, were developed during 1993 by this committee.<ref name="snodgrass9">Snodgrass, 1999, p. 9</ref> 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<ref>{{cite web | url = http://www.cs.arizona.edu/~rts/tsql2.html | title = TSQL2 Temporal Query Language | work = www.cs.arizona.edu | author = Richard T. Snodgrass | author-link = Richard T. Snodgrass | publisher = Computer Science Department of the University of Arizona | access-date = 14 July 2009}}</ref>

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.<ref name="snodgrass9"/> The TSQL2 approach was heavily criticized by [[Chris Date]] and [[Hugh Darwen]].<ref>Hugh Darwen, C.J. Date, “[http://citeseerx.ist.psu.edu/viewdoc/summary?doi=10.1.1.116.7598 An overview and Analysis of Proposals Based on the TSQL2 Approach]”, In ''Date on Database: Writings 2000-2006'', C.J. Date, Apress, 2006, pp. 481-514</ref> 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 [[datestamp]]s (DS) or [[date-timestamp]]s (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.<ref name="SQL2011"/>

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.