Valid time: Difference between revisions
m Substing templates: {{Kilde www}}. See User:AnomieBOT/docs/TemplateSubster for info. |
No edit summary |
||
| Line 5: | Line 5: | ||
The valid-time period is an interval based on event times, which are referred to as '''event [[Timestamp|datetime]]''' in [[data vault modeling|data vault]].<ref name=":0"/><ref>{{cite web |access-date=2024-02-10 |title=The Events API basics {{!}} Akeneo APIs |url=https://api.akeneo.com/events-documentation/subscription.html |work=api.akeneo.com}}<!-- auto-translated from Danish by Module:CS1 translator --></ref> Other names are '''application-time period'''<ref name=":0">{{cite web |title=A not-so-gentle follow-up on bitemporal data challenges - Roelant Vos |url=https://roelantvos.com/blog/a-not-so-gentle-follow-up-on-bitemporal-data-challenges/}}<!-- auto-translated from Danish by Module:CS1 translator --></ref> or '''real-world timeline'''.<ref name=":0" /> [[SQL:2011]] supports valid time through so-called '''application time-period tables'''.<ref>{{cite web |access-date=2024-06-18 |title=Illuminated Computing {{!}} Survey of SQL:2011 Temporal Features |url=https://illuminatedcomputing.com/posts/2019/08/sql2011-survey/ |work=illuminatedcomputing.com}}<!-- auto-translated from Danish by Module:CS1 translator --></ref><ref>{{cite web |access-date=2024-06-18 |language=en-us |title=Application-period temporal tables |url=https://www.ibm.com/docs/en/ias?topic=tables-application-period-temporal |work=www.ibm.com}}<!-- auto-translated from Danish by Module:CS1 translator --></ref><ref>{{cite web |access-date=2024-06-18 |title=Application-Time Periods |url=https://mariadb.com/kb/en/application-time-periods/ |work=MariaDB KnowledgeBase}}<!-- auto-translated from Danish by Module:CS1 translator --></ref><ref>{{cite web |access-date=2024-06-18 |title=SAP Help Portal |url=https://help.sap.com/docs/SAP_HANA_PLATFORM/3823b0f33420468ba5f1cf7f59bd6bd9/73c7b80318ba4405a8769e6ceb41ec64.html?version=2.0.05 |work=help.sap.com}}<!-- auto-translated from Danish by Module:CS1 translator --></ref> In a database table, valid-time is often represented by two extra table-columns, such as <code>start_validtime</code> and <code>end_validtime</code>. The time interval is [[Closed set|closed]] at its [[lower bound]] (denoted by <code>[</code>) and [[Open set|open]] at its [[upper bound]] (denoted by <code>)</code>). |
The valid-time period is an interval based on event times, which are referred to as '''event [[Timestamp|datetime]]''' in [[data vault modeling|data vault]].<ref name=":0"/><ref>{{cite web |access-date=2024-02-10 |title=The Events API basics {{!}} Akeneo APIs |url=https://api.akeneo.com/events-documentation/subscription.html |work=api.akeneo.com}}<!-- auto-translated from Danish by Module:CS1 translator --></ref> Other names are '''application-time period'''<ref name=":0">{{cite web |title=A not-so-gentle follow-up on bitemporal data challenges - Roelant Vos |url=https://roelantvos.com/blog/a-not-so-gentle-follow-up-on-bitemporal-data-challenges/}}<!-- auto-translated from Danish by Module:CS1 translator --></ref> or '''real-world timeline'''.<ref name=":0" /> [[SQL:2011]] supports valid time through so-called '''application time-period tables'''.<ref>{{cite web |access-date=2024-06-18 |title=Illuminated Computing {{!}} Survey of SQL:2011 Temporal Features |url=https://illuminatedcomputing.com/posts/2019/08/sql2011-survey/ |work=illuminatedcomputing.com}}<!-- auto-translated from Danish by Module:CS1 translator --></ref><ref>{{cite web |access-date=2024-06-18 |language=en-us |title=Application-period temporal tables |url=https://www.ibm.com/docs/en/ias?topic=tables-application-period-temporal |work=www.ibm.com}}<!-- auto-translated from Danish by Module:CS1 translator --></ref><ref>{{cite web |access-date=2024-06-18 |title=Application-Time Periods |url=https://mariadb.com/kb/en/application-time-periods/ |work=MariaDB KnowledgeBase}}<!-- auto-translated from Danish by Module:CS1 translator --></ref><ref>{{cite web |access-date=2024-06-18 |title=SAP Help Portal |url=https://help.sap.com/docs/SAP_HANA_PLATFORM/3823b0f33420468ba5f1cf7f59bd6bd9/73c7b80318ba4405a8769e6ceb41ec64.html?version=2.0.05 |work=help.sap.com}}<!-- auto-translated from Danish by Module:CS1 translator --></ref> In a database table, valid-time is often represented by two extra table-columns, such as <code>start_validtime</code> and <code>end_validtime</code>. The time interval is [[Closed set|closed]] at its [[lower bound]] (denoted by <code>[</code>) and [[Open set|open]] at its [[upper bound]] (denoted by <code>)</code>). |
||
In integration layers (for example a [[data warehouse]]), the valid time is controlled by the [[System of record|source system]] which delivers data to the data warehouse.<ref name=":1">{{cite web |title=A gentle introduction to bitemporal data challenges - Roelant Vos |url=https://roelantvos.com/blog/a-gentle-introduction-to-bitemporal-data-challenges/}}<!-- auto-translated from Danish by Module:CS1 translator --></ref> For many reasons, the valid timeline is different from the [[transaction time|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.<ref name=":1" /> In [[Bitemporal modeling|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 |
In integration layers (for example a [[data warehouse]]), the valid time is controlled by the [[System of record|source system]] which delivers data to the data warehouse.<ref name=":1">{{cite web |title=A gentle introduction to bitemporal data challenges - Roelant Vos |url=https://roelantvos.com/blog/a-gentle-introduction-to-bitemporal-data-challenges/}}<!-- auto-translated from Danish by Module:CS1 translator --></ref> For many reasons, the valid timeline is different from the [[transaction time|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.<ref name=":1" /> In [[Bitemporal modeling|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 modeling|dimensional model]] or [[Wide and narrow data|wide table]]) it is often desirable to have the data on only one timeline. |
||
== History == |
== History == |
||
Revision as of 17:29, 18 June 2024
 | This article needs additional citations for verification. (November 2008) |
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.
History
The term valid time was coined by Richard T. Snodgrass and his doctoral student (1986).[8]
As of December 2011, ISO/IEC 9075, Database Language SQL:2011 Part 2: SQL/Foundation included clauses in table definitions to define "application-time period tables" (that is, valid-time tables).
Example
[Needs an additional row: "John's death registered".]
| Date | What happened in the real world | Database action | What the database shows |
|---|---|---|---|
| 1975-04-03 | John is born | Nothing | There is no person called John Doe |
| 1975-04-04 | John's father officially reports John's birth | Inserted:Person(John Doe, Smallville) | John Doe lives in Smallville |
| 1994-08-26 | After graduation, John moves to Bigtown, but forgets to register his new address | Nothing | John Doe lives in Smallville |
| 1994-12-26 | Nothing | Nothing | John Doe lives in Smallville |
| 1994-12-27 | John registers his new address | Updated:Person(John Doe, Bigtown) | John Doe lives in Bigtown |
| 2001-04-01 | John dies | Deleted:Person(John Doe) | There is no person called John Doe |
Valid time is the time for which a fact is true in the real world. In the example above, the Person table gets two extra fields, valid_from and valid_to, specifying when a person's address was valid in the real world. On 1975-04-04, John's father proudly registered his son's birth. An official will then insert a new entry to the database stating that John lives in Smallville from April, 3rd. Notice that although the data was inserted on the 4th, the database states that the information is valid since the 3rd. The official does not yet know if or when John will ever move to another place so in the database the valid_to is filled with infinity (∞) or a very late date (like for example 2300-01-01). Resulting in this entry in the database:
| Person(John Doe, Smallville, 1975-04-03, ∞) |
On 1994-12-27 John reports his new address in Bigtown where he has been living since 1994-08-26. The Bigtown official does not change the address of the current entry of John Doe in the database. He adds a new one:
| Person (John Doe, Big Town, 1994-08-26, ∞) |
The original entry Person (John Doe, Smallville, 1975-04-03, ∞) is then updated (not removed!). Since it is now known that John stopped living in Smallville on 1994-08-26, the valid_to entry can be filled in. The database now contains two entries for John Doe
| Person(John Doe, Smallville, 1975-04-03, 1994-08-26) |
| Person(John Doe, Bigtown, 1994-08-26, ∞) |
When John dies the database is once more updated. The current entry will be updated stating the date of death as the last valid_to for Bigtown, as John does not live in Bigtown any longer. No new entry is being added. The database now looks like this:
| 1975-04-03-, 1994-08-26) |
| Person(John Doe, Bigtown, 1994-08-26, 2001-04-01) |
See also
References
- ^ a b c "A not-so-gentle follow-up on bitemporal data challenges - Roelant Vos".
- ^ "The Events API basics | Akeneo APIs". api.akeneo.com. Retrieved 2024-02-10.
- ^ "Illuminated Computing | Survey of SQL:2011 Temporal Features". illuminatedcomputing.com. Retrieved 2024-06-18.
- ^ "Application-period temporal tables". www.ibm.com. Retrieved 2024-06-18.
- ^ "Application-Time Periods". MariaDB KnowledgeBase. Retrieved 2024-06-18.
- ^ "SAP Help Portal". help.sap.com. Retrieved 2024-06-18.
- ^ a b "A gentle introduction to bitemporal data challenges - Roelant Vos".
- ^ Richard T. Snodgrass and Ilsoo Ahn, "Temporal Databases," IEEE Computer 19(9), September, 1986, pp. 35-42.
 | This computer-programming-related article is a stub. You can help Wikipedia by expanding it. |