Time is multi-dimensional. Valid time denotes the moment in time when a fact was true in reality where transaction time denotes the moment in time when this fact was true in the database. Relations considering both kinds of time are so called bi-temporal relations.
Multiple data models that consider time and extend the standard relational model have been been proposed since the early 1980's. Some only consider valid time or transaction time; some consider both. We are interested in the those including both since they allow for bi-temporal relations. Such data models consist of objects following the bi-temporal structure, a set of constraints over these objects, and operations compatible with the structure (Tsichritzis and Lochovsky 1982). Temporal query languages form a set of such operations and often extend traditional query languages. As example HSQL, TDM, TempSQL, TOSQL and TSQL(2) are based on SQL but are based on different data models (Snodgrass 1986).
These previous proposals resorted to prefixing queries to identify the temporal nature of the query. It is stated that these proposals did not specify any clear semantics which made it hard to comprehend the operations (Darwen and Date, n.d.).
In 1995, ISO (the International Organization for Standardization) started a project to support the SQL standard with temporal data (Kulkarni and Michels 2012). A temporal query language extension based on TSQL2 was submitted for standardization but failed to retrieve adequate support (Darwen and Date, n.d.). The ISO commitee found that TSQL2 departed too far from the design principels and relational database theory (Darwen and Date, n.d.). More precisely, TSQL2 detailed a major flaw with as result that data objects were in fact not relational. According Darwen and Date (n.d.) this applies to many other proposals considering temporal structures have similar flaws.
Indications of limited future adaptation by major relational database systems caused the project to support temporal data to be canceled in 2001 (Kulkarni and Michels 2012).
Later a language extension supporting temporal data was submitted and accepted by the ISO SQL committee. In December 2011 followed the publication of SQL:2011 (Zemke 2012) adding valid time and transaction time support by so called application-time and system-versioned tables respectively.
Application-time tables are ordinary table definitions using the PERIOD FOR EPeriod (EStart, EEnd) declaration where EStart and EEnd are DATE or timestamp type identifying the columns denoting the valid time period. Records for deletion or mutation of a certain period can be targeted via the FOR PORTION syntax which is compatible with the conventional UPDATE and DELETE statements. Application-time tables can be queried using conventional query syntax. Additionally the standard introduces the following conditional expressions that involve periods: CONTAINS, OVERLAPS, EQUALS, PRECEDES, SUCCEEDS, IMMEDIATELY PRECEDES, and IMMEDIATELY SUCCEEDS involve periods. (Kulkarni and Michels 2012)
System-versioned tables are also ordinary table definitions using the PERIOD FOR SYSTEM_TIME (Sys_Start, Sys_End) declaration with the keywords WITH SYSTEM VERSIONING. Similarly Sys_Start and Sys_End identify the transaction time period. Only records that contain the current time in their transaction time period will be targeted for mutation. The syntax FOR SYSTEM_TIME AS OF can be used to query the table content at a specific point of time. Additionally it is possible to query the content between any two points in time. (Kulkarni and Michels 2012)
Support for period joins, period aggregation, and non-temporal periods were left for future standards (Kulkarni and Michels 2012). These features are not present in the latest standard, SQL:2016.
PostgreSQL 9.2 supports most of the major features of SQL:2011 ("Documentation: 10: Appendix d. SQL Conformance," n.d.). While SQL Server 2016 only supports system-versioned tables (Rabeler 2016). Less restrictive however than the standard dictates. Records not containing the current time are not necessarily invalid. Similarly, MariaDB 10.3.4 (a popular MySQL fork) also supports system-versioned tables ("System-Versioned Tables," n.d.). Oracle 12 supports temporal functionality in (minimal) compliance with SQL:2011 ("Oracle Compliance to Core Sql:2011" 2017).
Adoption of application-time tables is never stated explicitly. Most modern relational database systems do however have native time and/or date types allowing similar temporal operations as in application-time tables. Even when these types or operations are not present (as in SQLite) typical temporal operations can still be realized by operating on integers representing time.
System-versioned tables seem to be more widely adopted than application-time tables. When support is missing, temporal data support can still be achieved as part of the application logic.
Darwen, Hugh, and C. J. Date. n.d. "An Overview and Analysis of Proposals Based on the Tsql2 Approach."
"Documentation: 10: Appendix d. SQL Conformance." n.d. PostgreSQL. https://www.postgresql.org/docs/10/static/features.html.
Kulkarni, Krishna, and Jan-Eike Michels. 2012. "Temporal Features in Sql:2011." SIGMOD Rec. 41 (3): 34--43. https://doi.org/10.1145/2380776.2380786.
"Oracle Compliance to Core Sql:2011." 2017. Database SQL Language Reference. https://docs.oracle.com/database/121/SQLRF/ap_standard_sql003.htm.
Rabeler, Carl. 2016. "Temporal Tables." Microsoft Docs. https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-2016.
Snodgrass, Richard Thomas. 1986. "Temporal Databases." IEEE Computer 19: 35--42.
"System-Versioned Tables." n.d. MariaDB KnowledgeBase. https://mariadb.com/kb/en/library/system-versioned-tables/.
Tsichritzis, Dionysios C., and Frederick H. Lochovsky. 1982. Data Models. Prentice Hall Professional Technical Reference.
Zemke, Fred. 2012. "What. S New in Sql: 2011." ACM SIGMOD Record 41 (1): 67--73.