Duration Modeling and Calculations

Data and time values represent the finest processing precision and do not reflect any duration; just one point in time is referenced. Using the DATE data type, a granularity of up to one second can be used. Based on the definition, the granularity precision can be of the order of nanoseconds when dealing with TIMESTAMP. Oracle does not provide a specific data type for dealing with date elements with no time element. Therefore, the meaning of the DATE data type value depends on precision, and time elements do not need to be taken into account. Typically, an employment contract is in days rather than hours. Consequently, dealing with more precise granularity frames, such as hours, minutes, or even seconds, is unnecessary. But they always need to be stored in the DATE value.

Similarly, an invoice due date is a day, the time is not essential. When we look at energy payments, they are usually made once a month, so we can use monthly granularity. Thus...

What timepoint means and how to use it

The moment in time is characterized by only one value expressing validity. It can be used to model either one timepoint or the start point of the duration. If the timepoint represents only one value, it must be ensured that a new state is inserted for each moment, regardless of the change. For example, a new data image must be entered into the database every month if monthly granularity is used. The management of the system is controlled by a plan (calendar) of events, which occurs periodically. These systems are suitable for obtaining a slice (image) of data in precisely defined periods but without further analysis of the changes in individual attributes. This limitation is just related to storage demands. Data is stored periodically, irrespective of the real change. As a result, many duplicates can be present. Expressing the duration by the end point is uncommon as it can be unknown or not very precise. People usually don’t know in advance...

Deploying duration models using timepoint borders

Although using timepoints in the system makes it possible to model the elapsed time duration, border identification can be demanding and too time-consuming; therefore, it is necessary to find the direct next state. The processing takes place in two phases. Firstly, the states where the validity begins after a defined timepoint (T) are identified, then the state with the lowest value of the beginning of validity is selected from this set. OBJ_REF expresses the referenced object identifier:

• First phase:

  SELECT Tab.*, RANK() OVER(ORDER BY BD) as rank
   FROM Tab
    WHERE BD>T and ID=OBJ_REF;

• Second phase:

  SELECT *
   FROM
     (SELECT Tab.*, RANK() OVER(ORDER BY BD) as rank
       FROM Tab
        WHERE BD>T and ID=OBJ_REF
      )
    WHERE rank=1;

Note that a solution where only the end date of the validity (state expiration...

Modeling unlimited validity

The reliability and security of the entire time-delimited system directly depend on the accuracy and quality of the data stored in it. The main emphasis is placed on the accuracy of individual values, undefined states, incorrect states, and validity limits.

As already mentioned, the validity of an object’s state is mostly modeled using a logical and physical time interval definition. The implemented solution contains two timeline values expressing the beginning and end of the validity concerning the interval representation. The start of the validity is commonly precisely specified. Therefore, it is necessary to know exactly when the validity of the new state begins. The end point of the validity can be more complicated. If a synchronization layer is present in the system and the data is modified with precisely defined time periodicity, the validity of each state can be clearly defined. However, the problem occurs when the data change is asynchronous...

Summary

In this chapter, we dealt with duration management. The discussion started with one timepoint modeling expressing the validity. This solution is strongly limited by the efficiency of data storage and subsequent data processing because many duplicate tuples can be present. However, by changing its meaning, the duration can be modeled. Namely, the timepoint can represent the beginning point of the validity. Thanks to that, each new state of the object automatically limits the validity of the direct predecessor. Valid future states can also be covered. There is, however, one strong limitation related to completely undefined states, which cannot be modeled, and special notation must be introduced.

Therefore, a general solution is provided by two timepoints representing the begin and end points of the validity duration frame. They can have various representations based on whether the begin and end points of the duration interval are part of the validity or not. The most often...

Questions

1. What is the main limitation of using only the beginning point of the validity coverage?
   1. It’s impossible to model completely undefined states
   2. It’s impossible to model valid future states
   3. Inefficiency caused by the duplicate tuples
   4. Significant costs to identify current valid states
2. Which model can change the granularity by limiting the gaps?
   1. Closed-closed
   2. Closed-open
   3. Open-closed
   4. All of the above
3. What is the output of the calculation ED - BD if both values are the DATE data types?
   1. Time elapsed in seconds
   2. Time elapsed in minutes
   3. Time elapsed in days
   4. Time elapsed in years
4. What is the output of the calculation ED - BD if both values are the TIMESTAMP data types?
   1. Time elapsed in seconds
   2. Time elapsed in days
   3. Value of the Interval Day to Second data type
   4. Value of the Interval Year to Month data type

Further reading

• Developing Time-Oriented Database Applications in SQL by Richard T. Snodgrass. It provides you with best practices for integrating past and current data in your applications.
• Time Granularities in Databases, Data Mining, and Temporal Reasoning by Claudio Bettini, Sushil Jajodia, and X. Sean Wang. It presents a technical framework for many issues in temporal databases, mostly related to duration, formats, and granularity levels.