Jump to content

Transaction time: Difference between revisions

From Wikipedia, the free encyclopedia
Browse history interactively
← Previous editNext edit →
Content deleted Content added
VisualWikitext
AnomieBOT (talk | contribs)
Bots
6,327,207 edits
Extend intro, add different common names, overview of common uses
Line 1: Line 1:
{{Refimprove|date=May 2008}}
{{Refimprove|date=May 2008}}


In [[temporal database]]s, '''transaction time''' (TT), like for example the '''[[Extract, transform, load|load]] [[date-timestamp|datetime]]''',<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}}<!-- auto-translated from Danish by Module:CS1 translator --></ref> is the time during which a fact stored in the database is considered to be true.
In [[temporal database]]s, '''transaction time''' is the time when some data has been [[Data loading|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 [[Data integration|integration layer]] (for example a [[data warehouse]]).<ref name=":0">{{Kilde www|url=https://roelantvos.com/blog/a-gentle-introduction-to-bitemporal-data-challenges/|tittel=A gentle introduction to bitemporal data challenges - Roelant Vos}}</ref> More formally, it is the [[point-in-time table|point-in-time]] during which a fact stored in the database is considered to be true.


The period is an interval based on [[Timestamp|load times]] (called '''load datetime''' in [[data vault modeling|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}}<!-- auto-translated from Danish by Module:CS1 translator --></ref>), also called '''inscription timestamp'''.<ref name=":0" /> Other names of the interval is '''assertion timeline'''<ref name=":1">{{Kilde www|url=https://roelantvos.com/blog/a-not-so-gentle-follow-up-on-bitemporal-data-challenges/|tittel=A not-so-gentle follow-up on bitemporal data challenges - Roelant Vos}}</ref>), '''[[State (computer science)|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>{{Kilde www|url=https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver16|tittel=Temporal Tables - SQL Server|besøksdato=2024-06-18|dato=2023-10-16|etternavn=rwestMSFT|språk=en-us|verk=learn.microsoft.com}}</ref><ref>{{Kilde www|url=https://mariadb.com/kb/en/system-versioned-tables/|tittel=System-Versioned Tables|besøksdato=2024-06-18|verk=MariaDB KnowledgeBase}}</ref><ref>{{Kilde www|url=https://help.sap.com/docs/SAP_HANA_PLATFORM/6b94445c94ae495c83a19646e7c3fd56/91302b26f62c4433bbc58e0a951cdc1d.html|tittel=SAP Help Portal|besøksdato=2024-06-18|verk=help.sap.com}}</ref><ref>{{Kilde www|url=https://www.ibm.com/docs/en/db2/11.1?topic=tables-system-period-temporal|tittel=System-period temporal tables|besøksdato=2024-06-18|språk=en-us|verk=www.ibm.com}}</ref>
⚫
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 set|closed]] <code>[</code> at its [[lower bound]] and [[open set|open]] <code>)</code> at its [[upper bound]].<ref>Kedar, S. V. (2013). Database management systems. Pune, India: Technical Publications.</ref>


For many reasons, transaction time (when data arrives from a [[System of record|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 modeling|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 modeling|dimensional model]] or [[Wide and narrow data|wide table]]) it is often desirable to have the data on only one timeline.
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.

⚫
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 set|closed]] <code>[</code> at its [[lower bound]] and [[open set|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 ==
== History ==

Revision as of 17:37, 18 June 2024

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] SQL: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 SQL:2011 Part 2: SQL/Foundation included clauses in table definitions to define "system-versioned tables" (that is, transaction-time tables).

See also

References

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

This computer-programming-related article is a stub. You can help Wikipedia by expanding it.

Retrieved from "https://en.wikipedia.org/w/index.php?title=Transaction_time&oldid=1229778150"