Valid time

In temporal databases, valid-time (VT), like for example an event datetime , ^[1] is the time period during which a database fact is valid in the modeled reality.

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 and open ) at its upper bound.

History

The term valid time was coined by Richard T. Snodgrass and his doctoral student (1986). ^[2]

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

• Transaction time
• Decision time
• Slowly changing dimension

References

1. "The Events API basics | Akeneo APIs". api.akeneo.com. Retrieved 2024-02-10.
2. Richard T. Snodgrass and Ilsoo Ahn, "Temporal Databases," IEEE Computer 19(9), September, 1986, pp. 35-42.