You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Martin Menzel <an...@hotmail.com> on 2007/03/04 19:34:01 UTC

OVERLAPS predicate, datetime artithmetic

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


Re: OVERLAPS predicate, datetime artithmetic

Posted by Stanley Bradbury <St...@gmail.com>.
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.