> Note the third row in the query result below is in error. The four hour
> interval (2300UTC - 0300UTC) does not overlap the interval 1530UTC-1627UTC).
> Is this a bug?
No. It conforms to (my reading of) the SQL99 spec. So it is a feature,
even if I misread the spec. Which I think I didn't ;) But if I did, then
we can change the implementation of course.
I've included the relevant part of the spec below. It seems clause (3)
requires that we reorder the arguments to OVERLAPS, though perhaps
someone would like to research whether TIME is allowed to be used with
OVERLAPS at all (if not, then we could make up the rules ourselves).
> It would be cool if timetz (or time) datatypes were to wrap properly
> across day boundaries (i.e. if start time < stop time then assume start time
> is day before) but at the very least, the overlaps functions should not lie
> to you!
Some parts of the spec aren't cool, or interfer with coolness. This may
be one of them. If everything conforms to the standard, then we can
start discussing whether that part of the standard is so brain-dead as
to be useless or likely to directly cause damage.
But in your case, choosing to record only times but then expecting the
code to respect a day boundary seems to be an assumption which could
bite you in other ways later. What happens when an interval happens to
be longer than a day??
hth
- Thomas
(omit some text defining the input as "(D1, E1) OVERLAPS (D2, E2)" as
the input to the OVERLAPS operator)
3) If D1 is the null value or if E1 < D1, then let S1 = E1 and let T1 = D1. Otherwise, let S1 = D1 and let T1 = E1.
4) Case: a) If the most specific type of the second field of <row value expression 2> is a datetime data type,
thenlet E2 be the value of the second field of <row value expression 2>. b) If the most specific type of the
secondfield of <row value expression 2> is INTERVAL, then let I2 be the value of the second field of <row
valueexpression 2>. Let E2 = D2 + I2.
5) If D2 is the null value or if E2 < D2, then let S2 = E2 and let T2 = D2. Otherwise, let S2 = D2 and let T2 = E2.
6) The result of the <overlaps predicate> is the result of the following expression: ( S1 > S2 AND NOT ( S1 >= T2
ANDT1 >= T2 ) ) OR ( S2 > S1 AND NOT ( S2 >= T1 AND T2 >= T1 ) ) OR
( S1 = S2 AND ( T1 <> T2 OR T1 = T2 ) )
