Transaction time
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[1][2]), also called inscription timestamp.[1] Other names of the interval is assertion timeline[3]), state timeline[3]) or technical timeline.[3] 2011 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.[1] 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 2011 Part 2: SQL/Foundation included clauses in table definitions to define "system-versioned tables" (that is, transaction-time tables).
See also
- Valid time, when an event in a database happened in the real world
- Decision time, when a decision was made about interpretation of history in a database
- Using transaction time
References
- ↑ 1.0 1.1 1.2 1.3 "A gentle introduction to bitemporal data challenges - Roelant Vos". Roelant Vos. 20 February 2023. https://roelantvos.com/blog/a-gentle-introduction-to-bitemporal-data-challenges/.
- ↑ "Transactional Links - AutomateDV". automate-dv.readthedocs.io. https://automate-dv.readthedocs.io/en/latest/tutorial/tut_t_links/.
- ↑ 3.0 3.1 3.2 "A not-so-gentle follow-up on bitemporal data challenges - Roelant Vos". Roelant Vos. 23 March 2023. https://roelantvos.com/blog/a-not-so-gentle-follow-up-on-bitemporal-data-challenges/.
- ↑ "Temporal Tables - SQL Server" (in en-us). learn.microsoft.com. 2023-10-16. https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver16.
- ↑ "System-Versioned Tables". MariaDB KnowledgeBase. https://mariadb.com/kb/en/system-versioned-tables/.
- ↑ "SAP Help Portal". help.sap.com. https://help.sap.com/docs/SAP_HANA_PLATFORM/6b94445c94ae495c83a19646e7c3fd56/91302b26f62c4433bbc58e0a951cdc1d.html.
- ↑ "System-period temporal tables" (in en-us). www.ibm.com. https://www.ibm.com/docs/en/db2/11.1?topic=tables-system-period-temporal.
- ↑ Kedar, S. V. (2013). Database management systems. Pune, India: Technical Publications.
- ↑ Snodgrass; Ilsoo Ahn (1986). "Temporal Databases". Computer 19 (9): 35. doi:10.1109/MC.1986.1663327. http://www2.cs.arizona.edu/~rts/pubs/Computer.pdf.
 |  |
