ALLEN RELATIONSHIP AND OTHER QUERIES - Managing Time in Relational Databases - page 319

The inverse of this relationship is: P 2 [overlaps 1 ]P 1 . In the
superscripted relationship, the first time period is the later one.
The predicate for this relationship, as it holds between two time
periods expressed as pairs of dates using the closed-open con-
vention, is:
(eff_beg_dt 1 > eff_beg_dt 2 )
AND (eff_beg_dt 1 < eff_end_dt 2 )
AND (eff_end_dt 1 > eff_end_dt 2 )
It says that P 1 starts after P 2 starts and before P 2 ends, and
ends after P 2 ends.
Consider the following request for information: which
policies began before the Diabetes Management Wellness Pro-
gram for 2009, and ended while that program was still going on?
The SQL written to fulfill this request is:
SELECT * FROM V_Allen_Example
WHERE pol_eff_beg_dt < wp_eff_beg_dt
AND pol_epis_end_dt > wp_eff_beg_dt
AND pol_epis_end_dt < wp_epis_end_dt
P 1 [ intersects ] P 2
This not an Allen relationship. It is the node in our taxonomy
of Allen relationships which includes the [starts], [starts 1 ],
[finishes], [finishes 1 ], [during], [during 1 ], [equals], [overlaps]
and [overlaps 1 ] relationships. In other words, it combines the
[overlaps] relationships with the [ fills ] relationships.
In the non-superscripted relationship, the first time period is
the earlier one. The predicate for this relationship, as it holds
between two time periods expressed as pairs of dates using the
closed-open convention, is:
(eff_beg_dt 1 <¼ eff_beg_dt 2 )
AND (eff_end_dt 1 > eff_beg_dt 2 )
It says that P 1 starts no later than P 2 starts, and ends after P 2
starts. The idea behind it is that it includes every relationship in
which P 1 and P 2 have at least one clock tick in common and in
which P 1 is the earlier time period.
The limiting case is that in which P 1 ends at the same time
P 2 starts. So let P 1 be [4/15/2010 - 5/13/2010] and let P 2 be
[5/12/2010 - 9/18/2010]. The clock tick they share is 5/12/2010.
The inverse of this relationship is: P 1 [ intersects 1 ]P 2 . The
first time period in this non-superscripted relationship is the
later one. The predicate for this relationship, as it holds between