Valid time

In temporal databases, valid-time is the time period when an event happened or something was true in the real world, or more formally when a fact was valid in the modeled reality.

The valid-time period is an interval based on event times, which are referred to as event datetime in data vault.[1][2] Other names are application-time period[1] or real-world timeline.[1] SQL:2011 supports valid time through so-called application time-period tables.[3][4][5][6] In a database table, valid-time is often represented by two extra table-columns, such as start_validtime and end_validtime. The time interval is closed at its lower bound (denoted by [) and open at its upper bound (denoted by )).

In integration layers (for example a data warehouse), the valid time is controlled by the source system which delivers data to the data warehouse.[7] For many reasons, the valid timeline is different from the transaction timeline (which is when data arrives in the warehouse), and it is important that the data warehouse is capable of unambiguously reporting what actually happened in the past by combining these two timelines.[7] In bitemporal data models, valid time and transaction time can be represented two-dimensionally in a Cartesian coordinate system. When data are delivered from the integration layer and is to be represented in a presentation layer (often in a dimensional model or wide table) it is often desirable to have the data on only one timeline.

  1. ^ a b c "A not-so-gentle follow-up on bitemporal data challenges - Roelant Vos".
  2. ^ "The Events API basics | Akeneo APIs". api.akeneo.com. Retrieved 2024-02-10.
  3. ^ "Illuminated Computing | Survey of SQL:2011 Temporal Features". illuminatedcomputing.com. Retrieved 2024-06-18.
  4. ^ "Application-period temporal tables". www.ibm.com. Retrieved 2024-06-18.
  5. ^ "Application-Time Periods". MariaDB KnowledgeBase. Retrieved 2024-06-18.
  6. ^ "SAP Help Portal". help.sap.com. Retrieved 2024-06-18.
  7. ^ a b "A gentle introduction to bitemporal data challenges - Roelant Vos".