You might have noticed during the introduction part at the beginning of this chapter that there are two kinds of temporal issues. The first one is the validity time of the proposition—a time period in which the proposition that a timestamped row in a table represents was actually true. For example, a contract with a supplier was valid only from time point 1 to time point 2. This kind of validity time is meaningful to people and meaningful for the business. The validity time is also called application time or human time. We can have multiple valid periods for the same entity. For example, the aforementioned contract that was valid from time point 1 to time point 2 might also be valid from time point 7 to time point 9.

The second temporal issue is the transaction time. A row for the contract mentioned previously was inserted in time point 1 and was the only version of the truth known to the database until somebody changed it, or even to the end of time. When the row is updated in time point 2, the original row is known as being true to the database from time point 1 to time point 2. A new row for the same proposition is inserted with a time valid for the database from time point 2 to the end of time. The transaction time is also known as system time or database time.

The database management systems (DBMSs) can, and should, maintain the transaction times automatically. The system has to take care to insert a new row for every update and change the transaction validity period in the original row. The system also needs to allow for querying the current and the historical data, and show the state at any specific point in time. There are not many additional issues with the transaction time. The system has to take care that the start time of the database time period is lower than the end time, and that the two periods in two rows for the same entity don't overlap. The database system has to know a single truth at a single point in time. Finally, the database does not care about the future. The end of the database time of the current row is actually the end of time. Database time is about the present and past states only.

Implementing application time might be much more complex. Of course, you might have validity time periods that end or even begin in the future. DBMSs can't take care of future times automatically, and for example check whether they are correct. Therefore, you need to take care of all the constraints you need. The DBMS can only help you by implementing time-aware objects, such as declarative constraints. For example, a foreign key from the products to the suppliers table, which ensures that each product has a supplier, could be extended to check not only whether the supplier for the product exists, but also if the supplier is a valid supplier at the time point when the foreign key is checked.

So far, I've talked about time as though it consists of discrete time points; I used the term time point as if it represented a single, indivisible, infinitely small point in time. Of course, time is continuous. Nevertheless, in common language, we talk about time as though it consists of discrete points. We talk in days, hours, and other time units; the granularity we use depends on what we are talking about. The time points we are talking about are actually intervals of time; a day is an interval of 24 hours, an hour is an interval of 60 minutes, and so on.

So what is the granularity level of the time points for the system and application intervals? For the system times, the decision is simple: use the lowest granularity level that a system supports. In SQL Server, with the datetime2 data type, you can support 100-nanosecond granularity. For application time, the granularity depends on the business problem. For example, for a contract with a supplier, the day level could work well. For measuring the intervals when somebody is using services, such as mobile phone services, the granularity of seconds could be more appropriate. This looks very complex. However, you can make a generalized solution for the application times. You can translate time points to integers, and then use a lookup table that gives you the context—gives the meaning to the integer time points.

Of course, you can also implement both application and system versioned tables. Such tables are called bitemporal tables.