Concept of Time in database

A database is used to model the state of some aspect of the real world outside in the form of relation. In general, database system store only one state that is the current state of the real world, and do not store data about previous and past states, except perhaps as audit trails. If the current state of the real world changes, the database gets modified and updated, and information about the past state gets lost.

However, in most of the real life applications, it is necessary to store and retrieve information about old states. For example, a student database must contain information about the previous performance history of that student for preparing the final result. An autonomous robotic system must store information about present and previous data of sensors from the environment for effective Action.

Example:

ID   name          dept name     salary     from             to 
10101   Srinivasan    Comp. Sci.        61000    2007/1/1   2007/12/31 
10101   Srinivasan    Comp. Sci.        65000    2008/1/1   2008/12/31 
12121   Wu            Finance           82000    2005/1/1   2006/12/31 
12121   Wu            Finance           87000    2007/1/1   2007/12/31 
12121   Wu            Finance           90000    2008/1/1   2008/12/31 
98345   Kim           Elec. Eng.        80000    2005/1/1   2008/12/31

In the above example, to simplify the representation, every row has only one time interval associated with it; thus, a row is represented once for every disjoint time interval in which it is true. Intervals that are given here are a combination of attributes from and to; an actual implementation would have a structured type, which is known as Interval, that contains both fields.

There are few important terminologies used in the concept of time in database:

1. Temporal database :
   Databases that store information about states of the real world across time are known as temporal databases.
2. Valid time :
   Valid time denotes the time period during which a fact is true with respect to the real world.
3. Transaction time :
   Transaction time is the time period during which a fact is stored in the databases.
4. Temporal relation :
   Temporal relation is one where each tuple has an associated time when it is true; the time may be either valid time or transaction time.
5. Bi-temporal relation :
   Both valid time and transaction time can be stored, in which case the relation is said to be a Bi-temporal relation.