Time and Time Again - Managing Time in Relational Databases, Part 26: Points in Time and Periods of Time

InfoManagement Direct, May 16, 2008

Tom Johnston, Randall Weis

Note: A glossary of technical terms used in these articles can be found on the Web sites of the authors. The URLs are MindfulData.com and InbaseInc.com.

This glossary is being constructed as a controlled vocabulary. By that we mean that any expression involving temporal concepts which is used in the definition of a glossary entry must itself be a glossary entry.

One reason for being this careful with definitions is that this kind of rigor must be applied to definitions before they can be "ontologized," i.e., expressed in a formal notation like first-order logic. That, in turn, is what is needed to make definitions available for manipulation by software-realized inferencing engines.

Another reason for being this careful with definitions is that this is the kind of rigor that must be applied to any set of technical definitions before we can claim not only that we say what we mean, but also that we know what we mean when we say it.

As this series continues, context becomes increasingly important so that the thread of the discussion is not lost. Figure 1 reviews what we've done so far.

Figure 1: Chart of Installments to Date in the Series (See PDF link at the end of the article.)

Last time, we began looking at original and temporal insert transactions but immediately digressed into a discussion of the problems that can be encountered when matching transactions to target tables. We planned to discuss both temporal entity integrity and temporal referential integrity as they apply to insert transactions in this installment. But we discovered that those discussions require another digression, this time into how we use pairs of dates to represent periods of time.

We also discovered that we have vacillated in this series between two methods of using pairs of dates to represent periods of time, methods which are called the "closed-closed" and "closed-open" approaches. Two other methods are possible: "open-closed" and "open-open." But we won't enter into an extensive discussion of all four methods, because that has already been covered by both Snodgrass and by Darwen and Lorentzos.^1,2

There is both a practical and a theoretical side to the question of how to represent time periods with dates. On the theoretical side, dates (or datetimes, or timestamps) are discrete points along a continuous timeline. And as Zeno first discovered, and Leibniz and Newton later formalized, the relationship between the continuous and the discrete is problematic, equally so for time as for space.

Points in Time and Periods of Time: Practical Issues

Consider the following two versions, V1 and V2, of the same object. Let us suppose that there is no gap in time between them, i.e., no gap along the "effectivity timeline." How are we to represent this? Two of the possibilities, the two specific ones we have vacillated between, are shown in Figures 2 and 3.

Figure 1: Chart of Installments to Date in this Series

Page 1 of 4.