Transaction time

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, 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.