Hi.
Can anyone help to understand the overlapping of period and date-time with examples and what is the way to handle these across timezones?
Period Operations Oracle Database
Types:
period [DATE, DATE)
Predicates:
p equals q p1 = q1 AND p2 = q2
p before q p2 < q1
p before -1 q q2 < p1
p meets q p2 = q1
p meets -1 q q2 = p1
p overlaps q p1 < q1 AND q1 < p2
p overlaps -1 q q1 < p1 AND p1 < q2
p during q q1 < p1 AND p2 < q2
p during -1 q p1 < q1 AND q2 < p2
p starts q p1 = q1 AND p2 < q2
p starts -1 q2 p1 = q1 AND q2 < p2
p finishes q q1 < p1 AND p2 = q2
p finishes -1 q p1 < q1 AND p2 = q2
p q p1 < q2 AND q1 < p2
p NULL p1 IS NULL
Datetime Constructors:
beginning(p) p1
previous(p) p1 - 1
last(p) p2 - 1
ending(p) p2
Interval Constructors:
duration(p) p2 - p1
Period Constructors:
p + i [p1 + j, p2 + j)
i + p [p1 + j, p2 + j)
p - i [p1 - j, p2 - j)
a extend b [LEAST(a, b), GREATEST(a + 1, b + 1))
p extend q [LEAST(p1, q1), GREATEST(p2, q2))
p extend a [LEAST(p1, a), GREATEST(p2, a + 1))
a extend p [LEAST(a, p1), GREATEST(a + 1, p2))
p \ q [GREATEST(p1, q1), LEAST(p2, q2))
p - q not possible
p [ q [LEAST(p1, q1), GREATEST(p2, q2))
p AT TIME ZONE i not supported
p AT LOCAL not supported
Other Operators:
CAST(a AS PERIOD) [a, a + 1/86400)
SQL permits a BETWEEN predicate for date times and intervals. Why such a predicate doesn't strictly apply to periods, as it is defined in terms of less-than. Does overlapping issues are only for periods, not date-time, and not between periods and date-time? Why five of the predicates are not possible between a date-time and a period: overlaps, overlaps -1, during-1, starts-1, and finishes -1?