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. 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) Due to disagreements within the ISO committee, the project responsible for temporal support was canceled near the end of 2001.

Concepts and constructs from SQL/Temporal have been implemented in IBM DB2, Oracle, Teradata, and PolarLake (see below); other products have included temporal support (see the list below). These ideas have also made their way into design patterns for things that change with time.

Implementation in IBM DB2

IBM DB2 10 for z/OS 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 almost identical to that proposed in SQL/Temporal.

Implementation in Oracle

Oracle 9i 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 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 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]

Implementation in Teradata

Teradata Database 13.10 includes 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.

Implementation in PolarLake Data Management Platform

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.”

Other Products Providing Temporal Support

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.

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).

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.

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.

ANSI and ISO Documents Related to SQL/Temporal

ANSI and ISO documents have the following prefixes.

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

ISO documents

More information on this effort may be requested from Richard Snodgrass, rts@cs.arizona.edu.