Databases Reference
In-Depth Information
should not be used by querying for the time period that contains
the date 5/23/2011. On the closed-open representation, it might
seem that we can't tell to which version 5/23/2011 belongs. But
in fact, we can. We just need the following WHERE clause predi-
cate in our SQL query:
WHERE D
1
<¼ '05/23/2011' AND '05/23/2011' < D
2
With this predicate, the query will correctly pick out V
2
from
Figure 3.2
.
So one reason we might have thought that the closed-closed
representation is right is that its begin and end dates are the same
dates we used to set up the example when we said that one period
begins and ends on one set of dates and the other period begins
and ends on the other set of dates. Another reason we might have
thought that the closed-closed representation is right is that we
are looking for a pair of dates that a third date is between.
Between, in one sense, is on display in the statement “Pick a
number between 1 and 10”. We all know that 1 and 10 are both
numbers that we can pick. And the SQL BETWEEN operator
corresponds to this sense of the word. So if we use the closed-
closed representation, we can write:
WHERE '05/23/2011' BETWEEN D
1
AND D
2
In other words, when a closed-closed representation is used,
we can rely on SQL's BETWEEN to express what we ordinarily
mean by “between”, which is what we might call the inclusive
sense of “between”.
But there is another sense of between, which is on display in
the statement “The abandoned car is somewhere between
mileposts 6 and 10, along I-65N, heading out of Pensacola”. We
all know that to find the car, we should start at milepost 6 and
continue up to milepost 10. In particular, we know that we don't
need to search past milepost 10, i.e. past the start of the tenth
mile heading out of Pensacola.
This is the sense of between used in the closed-open conven-
tion. The closest English equivalent would be “from . ....upto”,
in the sense of “from” and then “up to but not including”. But
since each SQL predicate returns the correct result, provided
each is used with its corresponding method of representing
periods of time, each method is correct.
What, then, is the advantage of using the closed-open repre-
sentation? Well, look again at the two ways of representing V
1
and V
2
. In both cases, V
1
and V
2
are contiguous. We know this
because we have set up the example that way. And with the
closed-open representation, we can immediately see that there
