The University of Arizona

TSQL2 and SQL3 Interactions



Various members of the temporal database research community have worked to transfer some of the constructs and insights of TSQL2 into SQL3.

The first step was to propose a new part to SQL3, termed SQL/Temporal. This new part was accepted at the Ottawa meeting in January, 1995 as Part 7 of the SQL3 specification. A modification of TSQL2's PERIOD data type is included in that part.

Discussions then commenced on adding valid-time and transaction-time support to SQL/Temporal. Two change proposals, ANSI-96-501 and ANSI-96-502, were unanimously accepted by ANSI and forwarded to ISO in early 1997, as MAD-146r2 (pdf) and MAD-147r2 (pdf), prepared for the ISO meeting in Madrid. A discussion of these proposals may be found in "Transitioning Temporal Support in TSQL2 to SQL3," by R. T. Snodgrass, M. H. Bohlen, C. S. Jensen, and A. Steiner, in Temporal Databases: Research and Practice, O. Etzion, S. Jajodia, and S. Sripada (eds.), Springer, pp. 150-194, 1998 (pdf). An etensive comparison between standard SQL and the proposed temporal support may be found in chapter 12 of Developing Time-Oriented Database Applications in SQL, Richard T. Snodgrass, Morgan Kaufmann Publishers, Inc., 1999 (pdf). Due to disagreements within the ISO committee, the project responsible for temporal support was canceled in 2001. However, concepts and constructs from SQL/Temporal were subsequently included in the SQL:2011 standard and have been implemented (via various syntaxes and semantics) in IBM DB2, MarkLogic Server, Microsoft SQLServer, Oacle, SAP HANA, and Teradata Database (see below); other products have included temporal support (see the list below for details). These ideas have also made their way into design patterns for things that change with time.

Temporal Facilities in the SQL Standard

ISO/IEC 9075, Database Language SQL:2011 Part 2: SQL/Foundation, published December 15, 2011 (and weighing in at 1434 pages), includes clauses in table definitions to define "application-time period tables" (essentially valid-time tables), with sequenced primary and foreign keys, This standard also includes single-table valid-time sequenced insertions, deletions, and updates. Nonsequenced valid-time queries are supported. The standard includes clauses for defining "system-versioned tables" (essentially transaction-time tables) with transaction-time current primary and foreign keys and supporting transaction-time current insertions, deletions, and updates as well as transaction-time current and nonsequenced queries. Finally, the standard supports "system-versioned application-time period tables" (a mouthful, essentially bitemporal tables) and supporting temporal queries and modifications of combinations of the valid-time and transaction-time variants just listed for uni-temporal tables.

Implementation in Oracle

Oracle 9i, released in 2001, included support for transaction time. Its flashback queries allow the application to access prior transaction-time states of their database; they are transaction timeslice queries. Database modifications and conventional queries are temporally upward compatible.

Oracle 10g, released in 2006, extended flashback queries to retrieve all the versions of a row between two transaction times (a key-transaction-time-range query) and allowed tables and databases to be rolled back to a previous transaction time, discarding all changes after that time. The Oracle 10g Workspace Manager includes the period data type, valid-time support, transaction-time support, support for bitemporal tables, and support for sequenced primary keys, sequenced uniqueness, sequenced referential integrity, and sequenced selection and projection, in a manner quite similar to that proposed in SQL/Temporal.

These facilities permit tracing of actions on data as well as the ability to perform database forensics, as elaborated in the book "Oracle Forensics: Oracle Security Best Practices", by Paul M. Wright.

Oracle 11g, released in 2007, does not rely on transient storage like the undo segments. Rather, it records changes in the Flashback Recovery Area. Validtime queries were also enhanced; see Workspace Manager Valid Time Support. [Oracle documentation]

Oracle 12c, released in 2013, continues to support transaction time via the Workspace Manager, implemented as a PL/SQL package, while also supporting the (multi-)temporal features introduced in SQL:2011. (It seems that the valid-time support of prior versions of Workspace Manager has been removed, in favor of the SQL:2011 approach, though the enable_at_valid_time() procedure can specifiy various options for the visibility of table data.) The valid-time aspect (termed "temporal validity") of Oracle 12c is illustrated in an article and a tutorial. A blog post touches on transaction time implemented with the Flashback Data Archive, valid time implemented with temporal validity, and a third dimension called "decision time" also implemented with temporal validity. That blog post cautions that "The support for temporal data management in Oracle 12c is based on sound concepts, but the implementation is currently incomplete. I miss mainly a temporal DML API, temporal integrity constraints, temporal joins and temporal aggregations."

Implementation in Teradata

Teradata Database 13.10, released October 2010, introduced the period data type, valid-time support, transaction-time support, timeslices, temporal upward compatibility, sequenced primary key and temporal referential integrity constraints, nonsequenced queries, and sequenced projection and selection, in a manner almost identical to that proposed in SQL/Temporal.

Teradata Database 14, released February 29, 2012, adds capabilities to create a global picture of an organization's business at any point in time.

Implementation in IBM DB2

IBM DB2 10 for z/OS, released in October 2010 for z/OS and in April 2012 for Linux, Unix, and Windows, includes the period data type, valid-time support (termed business time), transaction-time support (termed system time), timeslices, temporal upward compatibility, sequenced primary keys, and sequenced projection and selection, in a manner identical to that in SQL:2011. Here is a tutorial on temporal tables in DB2.

Implementation in Microsoft SQLServer

Microsoft SQLServer 2016 provides support for a transaction-time backing table recording the history of a designated table, termed a "system-versioned temporal table".

Implementation in SAP HANA

SAP HANA runs on SAP In-Memory Computing Engine, which provides support for History Tables. Such tables provide support for session level and statement level time travel, effectively a transaction-time slice.

Implementation in MarkLogic Server

MarkLogic's MarkLogic Server product stores XML documents as a transaction-time database and supports transaction timeslice queries in XQuery (termed "point-in-time queries"), almost identically to that proposed in SQL/Temporal and applied to XQuery in τXQuery.

Other Products Providing Temporal Support

Stratio released in August 2015 a new version of their open source Lucene index plugin for Apache Cassandra. "One of its features is to index bitemporal data using the 4-R index approach (Bliujute, R. et al.) over four Lucene's date range prefix trees. Each Cassandra node indexes its own local data, allowing to efficiently manage large amounts of bitemporal data spread over hundreds of nodes. It can also be combined with MapReduce frameworks such as Spark and Hadoop to perform massive computations." This brings valid time and transaction time as well as valid-time+transaction-time slices into Big Data.

Tom Johnston and Randy Weis have written a book entitled "Managing Time in Relational Databases" (Morgan Kaufmann) in which they present a novel approach called asserted versioning (also described on the book's web page). In this approach, bitemporal requirements are expressed separately and declaratively, in metadata tables.

Tom Johnston has written a book entitled "Bitemporal Data: Theory and Practice" (Morgan Kaufmann) proposing three temporal dimensions: state time (i.e., valid time), inscription time (i.e., transaction time), and speech act time (originally called assertion time in his previous book).

Asserted Versioning, LLC has developed a set of macros in ERWin that provide support for asserted versioning, included deferred assertions. These macros also provide full temporal upward compatibility and encapsulate the temporal complexity of primary key and foreign keys and of modifications (those not having complex predicates involving other temporal tables) over bitemporal tables (valid and transaction time). One-table updates (without a where clause) are allowed, with sequenced primary key and referential integrity constraints maintained across such updates. Non-sequenced queries are supported by making the begin and end dates explicit. One-table sequenced updates are also supported. Sequenced queries are not yet supported, nor are sequenced updates that reference other tables; those have to be mapped by the user into non-sequenced statements.

IBM's DataPropagator can use data replication of a DB2 log to create both before and after images of every row modification to create a transaction-time database that can be later queried.

LogExplorer from Lumigent provides an analysis tool for Microsoft SQLServer logs, to allow one to view how rows change over time (a nonsequenced transaction-time query) and then to selectively back out and replay changes, on both relational data and the schema (it effectively treats the schema as a transaction-versioned schema).

TimeDB is a Java API and uses JDBC to run as a frontend for Oracle. Temporal statements (queries, updates, and assertions) are compiled into (sequences of) SQL-92 statements which are executed by the backend. TmeDB provides upward compatibility, temporal upward compatibility, and bitemporal support (valid time and transaction time).

aTempo's Time Navigator is a data replication tool for DB2, Oracle, Microsoft SQL Server and Sybase that extracts information from a database to build a slice repository, thereby enabling image-based restoration of a past slice; these are transaction time-slice queries.

The PolarLake Data Management Platform supports bitemporal data in financial applications, as announced on June 9, 2011. Commenting on the release Warren Buckley, founder and CTO of PolarLake said, “The Investment Banking and Asset Management communities are facing new levels of transparency requirements from Regulators when it comes to Financial Data. Regulators will be keen to know what you knew and when you knew it about particular Data Entities. They will also want to know when your view of that Data Entity changed over time, with detailed history of the changes.”

The Datomic distributed database retains the transaction-time history of every object.

A constantly growing list of implementations can be found at the Wikipedia Temporal database entry.

ANSI and ISO Documents Related to SQL/Temporal

ANSI and ISO documents have the following prefixes.

  • ansi-94: To ANSI in 1994
  • ansi-95: To ANSI in 1995
  • ansi-96: To ANSI in 1996
  • ansi-97: To ANSI in 1997
  • RIO: To ISO for the Rio de Janeiro meeting in January, 1995
  • OTT: To ISO for the Ottawa meeting in July, 1995
  • LHR: To ISO for the London meeting in January, 1996
  • MCI: To ISO for the Kansas City meeting, May, 1996
  • MAD: To ISO for the Madrid meeting, January-February, 1997

The general procedure is that change proposals are first directed towards a national committee (i.e., ANSI X3H2), which then decides whether to forward the proposal to the international committee (ISO/IEC JTC 1/SC 21/WG 3 DBL).

The .pdf files are Adobe PDF; they can be read via the reader freely accessible at http://www.adobe.com/prodindex/acrobat/readstep.html. The .ps files are postscript files.

ANSI documents

  • ANSI-97-010 Richard T. Snodgrass, Response to MAD-220, 13pp., January 21, 1997. (Identical to ANSI-97-101.) [ps, pdf]
  • ANSI-97-011 Richard T. Snodgrass, A Second Addendum to Valid- and Transaction-Time Proposals, 7pp., January 21, 1997. [ps, pdf]
  • ANSI-96-013 Richard T. Snodgrass, A Road Map of Additions to SQL/Temporal, 9pp., February 9, 1996. [ps, pdf]
  • ANSI-96-014 Richard T. Snodgrass, Michael H. Bohlen, Christian S. Jensen, and Andreas Steiner, Adding Valid Time -- Part A, 41 pp., February 21, 1996. (revision of lhr096) [ps, pdf]
  • ANSI-96-151 Richard T. Snodgrass, Michael H. Bohlen, Christian S. Jensen, and Andreas Steiner, Adding Valid Time to SQL/Temporal, 68pp., April 11, 1996. (revision of ansi-96-014) [ps, pdf]
  • ANSI-96-152 Richard T. Snodgrass, Michael H. Bohlen, Christian S. Jensen, and Andreas Steiner, Adding Transaction Time to SQL/Temporal, 44pp. April 15, 1996. [ps, pdf]
  • ANSI-96-501r2 Richard T. Snodgrass, Michael H. Bohlen, Christian S. Jensen, and Andreas Steiner, Adding Valid Time to SQL/Temporal, 77pp., October 22, 1996. (revision of MCI-142) [ps, pdf]
  • ANSI-96-502r2 Richard T. Snodgrass, Michael H. Bohlen, Christian S. Jensen, and Andreas Steiner, Adding Transaction Time to SQL/Temporal, 47pp. October 22, 1996. (revision of MCI-143) [ps, pdf]
  • ANSI-96-503 Richard T. Snodgrass, Summary of Research Results on Implementing Temporal Support in Relational Databases, 11pp., October 22, 1996. [ps, pdf]
  • ANSI-96-582 Richard T. Snodgrass, Addendum to Valid- and Transaction-time Proposals, 8pp., November 22, 1996. [ps, pdf]
  • ANSI-95-485 Richard T. Snodgrass, Adding Valid Time---Part A
  • ANSI-95-486 Richard T. Snodgrass, Response to LHR-042, "Possible problems in SQL/T", 14pp., December 6, 1995. [ps, pdf]
  • ANSI-95-487 Richard T. Snodgrass, Response to LHR-043, "Fixing possible problems in SQL/T", 5 pp., December 6, 1995. [ps, pdf]
  • ANSI-94-276 Cliff Leung and Nelson Mattos, TSQL2, June 1, 1994. [ps, pdf]
  • ANSI-94-xxx Richard T. Snodgrass, editor, Adding Valid Time to SQL3 (excerpt of TSQL2 syntax from book), 39 pp. [ps, pdf]

ISO documents

  • MAD-146r2 Richard T. Snodgrass, Michael H. Bohlen, Christian S. Jensen, and Andreas Steiner, Adding Valid Time to SQL/Temporal, 77 pp., November 21, 1996.(identical to ANSI-96-501r2) [ps, pdf]
  • MAD-147r2 Richard T. Snodgrass, Michael H. Bohlen, Christian S. Jensen, and Andreas Steiner, Adding Transaction Time to SQL/Temporal, 47 pp., November 21, 1996. (identical to ANSI-96-502r2) [ps, pdf]
  • MAD-148 Richard T. Snodgrass, Summary of Research Results On Implementing Temporal Support in Relational Databases, 11 pp., November 21, 1996. (identical to ANSI-96-503) [ps, pdf]
  • MAD-203 Richard T. Snodgrass, Addendum to Valid- and Transaction-time Proposals, 8pp., December 13, 1996. (identical to ANSI-96-582) [ps, pdf]
  • MAD-245 Richard T. Snodgrass, Response to MAD-220, 13pp., June 4, 1997. (identical to ANSI-97-010) [ps, pdf]
  • MCI-099 Richard T. Snodgrass, A Road Map of Additions to SQL/Temporal, 9pp., APril 9, 1996. (revision of ANSI-96-013) [ps, pdf]
  • MCI-142 Richard T. Snodgrass, Michael H. Bohlen, Christian S. Jensen, and Andreas Steiner, Adding Valid Time to SQL/Temporal, 77 pp., May 10, 1996.(revision of ANSI-96-151) [ps, pdf]
  • MCI-143 Richard T. Snodgrass, Michael H. Bohlen, Christian S. Jensen, and Andreas Steiner, Adding Transaction Time to SQL/Temporal, 49pp., May 10, 1996. (revision of ANSI-96-152) [ps, pdf]
  • MCI-144 Richard T. Snodgrass, Road Map Queries in the US and UK Proposals, 6pp, May 10, 1996. (Also ANSI-96-264r1) [ps, pdf]
  • RIO-075 Richard T. Snodgrass, editor, SQL/Temporal Base Document Proposal (excerpt and revision of ANSI-94-xxx), 41pp., 1994. [ps, pdf]
  • LHR-092 Richard T. Snodgrass, Response to LHR-042, "Possible problems in SQL/T", 18pp., December 29. 1995. (identical to ANSI-486) [ps, pdf]
  • LHR-093 Richard T. Snodgrass, Response to LHR-043, "Fixing possible problems in SQL/T", 5 pp., December 29. 1995. (identical to ANSI-487) [ps, pdf]
  • LHR-096 Richard T. Snodgrass, Adding Valid Time --- Part A, 40pp., December 29. 1995. (identical to ANSI-95-485) [ps, pdf]