# Transaction time

> Mediated Wiki article. Canonical URL: https://mediated.wiki/source/Transaction_time
> Markdown URL: https://mediated.wiki/source/Transaction_time.md
> Source: https://en.wikipedia.org/wiki/Transaction_time
> Source revision: 1262556514
> License: Creative Commons Attribution-ShareAlike 4.0 International (https://creativecommons.org/licenses/by-sa/4.0/)

This article needs additional citations for verification. Please help improve this article by adding citations to reliable sources. Unsourced material may be challenged and removed. Find sources: "Transaction time" – news · newspapers · books · scholar · JSTOR (May 2008) (Learn how and when to remove this message)

In [temporal databases](/source/Temporal_database), **transaction time** is the time when some data has been [loaded](/source/Data_loading) into a [database](/source/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](/source/Data_integration) (for example a [data warehouse](/source/Data_warehouse)).[1] More formally, it is the [point-in-time](https://en.wikipedia.org/w/index.php?title=Point-in-time_table&action=edit&redlink=1) during which a fact stored in the database is considered to be true.

The period is an interval based on [load times](/source/Timestamp) (called **load datetime** in [data vault](/source/Data_vault_modeling)[1][2]), also called **inscription timestamp**.[1] Other names of the interval is **assertion timeline**[3]), **[state timeline](/source/State_(computer_science))**[3]) or **technical timeline**.[3] [SQL:2011](/source/SQL%3A2011) 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](/source/System_of_record)) is almost always different from [valid time](/source/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](/source/Bitemporal_modeling) data models, valid-time and transaction time can be represented two-dimensionally in a [Cartesian coordinate system](/source/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](/source/Dimensional_modeling) or [wide table](/source/Wide_and_narrow_data)) 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](/source/Closed_set) [ at its [lower bound](/source/Lower_bound) and [open](/source/Open_set) ) at its [upper bound](/source/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](/source/Relational_database_management_systems) (RDBMS) have represented until_changed with the largest timestamp supported or the keyword forever. This convention is a technical [workaround](/source/Workaround), and not technically precise.

## History

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

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

## See also

- [Valid time](/source/Valid_time), when an event in a database happened in the real world

- [Decision time](/source/Decision_time), when a decision was made about interpretation of history in a database

- [Using transaction time](/source/Temporal_database#Using_transaction_time)

## References

1. ^ [***a***](#cite_ref-:0_1-0) [***b***](#cite_ref-:0_1-1) [***c***](#cite_ref-:0_1-2) [***d***](#cite_ref-:0_1-3) ["A gentle introduction to bitemporal data challenges - Roelant Vos"](https://roelantvos.com/blog/a-gentle-introduction-to-bitemporal-data-challenges/). *Roelant Vos*. 20 February 2023.

1. **[^](#cite_ref-2)** ["Transactional Links - AutomateDV"](https://automate-dv.readthedocs.io/en/latest/tutorial/tut_t_links/). *automate-dv.readthedocs.io*. Retrieved 2024-02-10.

1. ^ [***a***](#cite_ref-:1_3-0) [***b***](#cite_ref-:1_3-1) [***c***](#cite_ref-:1_3-2) ["A not-so-gentle follow-up on bitemporal data challenges - Roelant Vos"](https://roelantvos.com/blog/a-not-so-gentle-follow-up-on-bitemporal-data-challenges/). *Roelant Vos*. 23 March 2023.

1. **[^](#cite_ref-4)** rwestMSFT (2023-10-16). ["Temporal Tables - SQL Server"](https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver16). *learn.microsoft.com*. Retrieved 2024-06-18.

1. **[^](#cite_ref-5)** ["System-Versioned Tables"](https://mariadb.com/kb/en/system-versioned-tables/). *MariaDB KnowledgeBase*. Retrieved 2024-06-18.

1. **[^](#cite_ref-6)** ["SAP Help Portal"](https://help.sap.com/docs/SAP_HANA_PLATFORM/6b94445c94ae495c83a19646e7c3fd56/91302b26f62c4433bbc58e0a951cdc1d.html). *help.sap.com*. Retrieved 2024-06-18.

1. **[^](#cite_ref-7)** ["System-period temporal tables"](https://www.ibm.com/docs/en/db2/11.1?topic=tables-system-period-temporal). *www.ibm.com*. Retrieved 2024-06-18.

1. **[^](#cite_ref-8)** Kedar, S. V. (2013). Database management systems. Pune, India: Technical Publications.

1. **[^](#cite_ref-9)** Snodgrass; Ilsoo Ahn (1986). ["Temporal Databases"](http://www2.cs.arizona.edu/~rts/pubs/Computer.pdf) (PDF). *Computer*. **19** (9): 35. [doi](/source/Doi_(identifier)):[10.1109/MC.1986.1663327](https://doi.org/10.1109%2FMC.1986.1663327).

v t e Database models Common models Flat Hierarchical Dimensional Network Relational Entity–relationship Enhanced Graph Object-oriented Entity–attribute–value Other models Multi-dimensional Array Semantic Star schema XML database Implementations Flat file Column-oriented Document-oriented Object–relational Deductive Temporal Valid time Transaction time Decision time XML data store Key–value store Ordered Key-Value Store Triplestore

This computer-programming-related article is a stub. You can help Wikipedia by adding missing information.

- [v](https://en.wikipedia.org/wiki/Template:Compu-prog-stub)
- [t](/source/Template_talk%3ACompu-prog-stub)
- [e](https://en.wikipedia.org/wiki/Special:EditPage/Template:Compu-prog-stub)

---
Adapted from the Wikipedia article [Transaction time](https://en.wikipedia.org/wiki/Transaction_time) by Wikipedia contributors ([contributor history](https://en.wikipedia.org/wiki/Transaction_time?action=history)). Available under [Creative Commons Attribution-ShareAlike 4.0 International](https://creativecommons.org/licenses/by-sa/4.0/). Changes may have been made.