Temporal data support was introduced in the SQL:2011—ANSI standard. There were also attempts to define support in the previous standard versions, but without success (TSQL2 extensions in 1995). They were not widely accepted and vendors did not implement them.

The SQL:2011 standard proposed that temporal data should be supported in relational database management systems. A very important thing is that SQL:2011 did not introduce a new data type to support temporal data; rather, it introduced the period.

A period is a table attribute and it's defined by two table columns of date type, representing start time and end time, respectively. It is defined as follows:

• A period must have a name.
• The end time must be greater than the start time.
• It is a closed-open period model. The start time is included in the period and the end time is excluded.

The SQL:2011 standard recognizes two dimensions of temporal data support:

• Application time or valid time tables
• System time or transaction time tables

Application time period tables are intended to meet the requirements of applications that capture time periods during which the data is believed to be valid in the real world. A typical example of such an application is an insurance application, where it is necessary to keep track of the specific policy details of a given customer that are in effect at any given point in time.

System-versioned tables are intended to meet the requirements of applications that must maintain an accurate history of data changes either for business reasons, legal reasons, or both. A typical example of such an application is a banking application, where it is necessary to keep previous states of customer account information so that customers can be provided with a detailed history of their accounts. There are also plenty of examples where certain institutions are required by law to preserve historical data for a specified length of time to meet regulatory and compliance requirements.

Bitemporal tables are tables that implement both application time and system-versioned time support.

After the standard was published, many vendors came up with the temporal table implementation:

• IDM DB2 10 added full support for temporal tables (for both application time and system-versioned).
• Oracle implemented a feature called the Flashback Data Archive (FDA). It automatically tracks all changes made to data in a database and maintains an archive of historical data. Oracle 12c introduced valid time temporal support.
• PostgreSQL does not support temporal tables natively, but temporal tables approximate them.
• Teradata implements both valid time and transaction time table types based on the TSQL2 specification.

All these implementations most probably affected Microsoft's decision to implement temporal tables in SQL Server 2016.