Transaction time explained

In temporal databases, transaction time is the time when some data has been loaded into a database. The time when a transaction is valid can be called the transaction time-period. It is a technical timeline controlled by a integration layer (for example a data warehouse).[1] More formally, it is the point-in-time during which a fact stored in the database is considered to be true.

The period is an interval based on load times (called load datetime in data vault[2]), also called inscription timestamp. Other names of the interval is assertion timeline[3]), state timeline) or technical timeline. has support for transaction time through so-called system-versioned tables.[4] [5] [6] [7]

For many reasons, transaction time (when data arrives from a source system) is almost always different from valid time (when the event happened in the real world). For a data warehouse to unambiguously report what actually happened in the past it must be able to combine these two timelines. In bitemporal data models, valid-time and transaction time can be represented two-dimensionally in a Cartesian coordinate system. When data is delivered from the integration layer and is to be presented in a presentation layer (often in a dimensional model or wide table) it is often desirable to have the data on only one timeline.

In a database table, the transaction time is often represented as an interval allowing the system to "remove" entries by using two table-columns start_tt and end_tt. The time interval is closed [ at its lower bound and open ) at its upper bound.[8] When the ending transaction time is unknown, it may be considered as until_changed. Academic researchers and some relational database management systems (RDBMS) have represented until_changed with the largest timestamp supported or the keyword forever. This convention is a technical workaround, and not technically precise.

History

The term transaction time was coined by Richard T. Snodgrass and his doctoral student Ilsoo Ahn (1986).[9]

As of December 2011, ISO/IEC 9075, Database Language Part 2: SQL/Foundation included clauses in table definitions to define "system-versioned tables" (that is, transaction-time tables).

See also

Notes and References

  1. Web site: A gentle introduction to bitemporal data challenges - Roelant Vos .
  2. Web site: 2024-02-10 . Transactional Links - AutomateDV . automate-dv.readthedocs.io.
  3. Web site: A not-so-gentle follow-up on bitemporal data challenges - Roelant Vos .
  4. Web site: 2024-06-18 . 2023-10-16 . en-us . rwestMSFT . Temporal Tables - SQL Server . learn.microsoft.com.
  5. Web site: 2024-06-18 . System-Versioned Tables . MariaDB KnowledgeBase.
  6. Web site: 2024-06-18 . SAP Help Portal . help.sap.com.
  7. Web site: 2024-06-18 . en-us . System-period temporal tables . www.ibm.com.
  8. Kedar, S. V. (2013). Database management systems. Pune, India: Technical Publications.
  9. Snodgrass . Ilsoo Ahn . Temporal Databases . Computer . 19 . 9 . 35 . 1986 . 10.1109/MC.1986.1663327.