Martin Menzel wrote:
Hi all,
The Derby documentation says about the OVERLAPS predicate that it “can
be done with Java methods”.
Could someone please give an example on how to do this? I need to find
out if a timeframe that is calculated in my application (start and end
datetimes known as Java Date objects) overlaps with any timeframes
stored in the db. My table has the fields start_datetime (type
TIMESTAMP) and duration_in_milliseconds (type BIGINT). There is no
“end_time” field as such. Duration can be NULL in the db, then it
shall be assumed the duration is one hour.
How can I write this query?
Any help greatly appreciated, thanks.
Martin
Hi Martin -
I may have missed something as I am not familiar with OVERLAP but
assuming it returns a boolean that indicates if any of the dates in the
two ranges overlap you could use this qualification:
where ({fn TIMESTAMPADD( SQL_TSI_FRAC_SECOND, -tableDuration,
mystart-date)}) <= TableEndDate
and (myend-date >= TableStartDate)
Check if SQL_TSI_FRAC_SECOND is milliseconds or some other value and
adjust accordingly.
The above applies the function to the value you compute to obtain better
performance
CAUTION in the MANUAL:
You should not put a datetime column inside of a timestamp arithmetic
function in
WHERE clauses because the optimizer will not use any index on the column.
Let me know if this works for you.
