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).<ref name=":0">{{cite news |title=A gentle introduction to bitemporal data challenges - Roelant Vos |newspaper=Roelant Vos |date=20 February 2023 |url=https://roelantvos.com/blog/a-gentle-introduction-to-bitemporal-data-challenges/}}</ref> 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<ref name=":0" /><ref>{{cite web |access-date=2024-02-10 |title=Transactional Links - AutomateDV |url=https://automate-dv.readthedocs.io/en/latest/tutorial/tut_t_links/ |work=automate-dv.readthedocs.io}}</ref>), also called '''inscription timestamp'''.<ref name=":0" /> Other names of the interval is '''assertion timeline'''<ref name=":1">{{cite news |title=A not-so-gentle follow-up on bitemporal data challenges - Roelant Vos |newspaper=Roelant Vos |date=23 March 2023 |url=https://roelantvos.com/blog/a-not-so-gentle-follow-up-on-bitemporal-data-challenges/}}</ref>), '''state timeline'''<ref name=":1" />) or '''technical timeline'''.<ref name=":1" /> SQL:2011 has support for transaction time through so-called '''system-versioned tables'''.<ref>{{cite web |access-date=2024-06-18 |date=2023-10-16 |language=en-us |surname=rwestMSFT |title=Temporal Tables - SQL Server |url=https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver16 |work=learn.microsoft.com}}</ref><ref>{{cite web |access-date=2024-06-18 |title=System-Versioned Tables |url=https://mariadb.com/kb/en/system-versioned-tables/ |work=MariaDB KnowledgeBase}}</ref><ref>{{cite web |access-date=2024-06-18 |title=SAP Help Portal |url=https://help.sap.com/docs/SAP_HANA_PLATFORM/6b94445c94ae495c83a19646e7c3fd56/91302b26f62c4433bbc58e0a951cdc1d.html |work=help.sap.com}}</ref><ref>{{cite web |access-date=2024-06-18 |language=en-us |title=System-period temporal tables |url=https://www.ibm.com/docs/en/db2/11.1?topic=tables-system-period-temporal |work=www.ibm.com}}</ref>

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.<ref name=":0" /> 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 <code>start_tt</code> and <code>end_tt</code>. The time interval is closed <code>[</code> at its lower bound and open <code>)</code> at its upper bound.<ref>Kedar, S. V. (2013). Database management systems. Pune, India: Technical Publications.</ref> When the ending transaction time is unknown, it may be considered as <code>until_changed</code>. Academic researchers and some relational database management systems (RDBMS) have represented <code>until_changed</code> with the largest timestamp supported or the keyword <code>forever</code>. 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).<ref>{{Cite journal | last1 = Snodgrass | last2 = Ilsoo Ahn | title = Temporal Databases | journal = Computer | volume = 19 | issue = 9 | pages = 35 | year = 1986 | doi = 10.1109/MC.1986.1663327| url = http://www2.cs.arizona.edu/~rts/pubs/Computer.pdf }}</ref>

As of December 2011, ISO/IEC 9075, Database Language SQL: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== {{Reflist}}

{{DEFAULTSORT:Transaction Time}} Category:Database management systems Category:Transaction processing