db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Stanley Bradbury <Stan.Bradb...@gmail.com>
Subject Re: OVERLAPS predicate, datetime artithmetic
Date Tue, 06 Mar 2007 23:35:53 GMT
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.


Mime
View raw message