overlap function in informix sql
Just recently I learned that there are built-in functions in sql server, oracle which detect overlap between two date / date-time ranges. When will the same be available for informix ids ? For e.g in some table if there are records as shown below. ============================================ ID Date Start Time End Time -------------------------------------------- 1 01/01/2012 10:00 12:00 2 01/01/2012 11:00 15:00 3 02/01/2012 10:00 15:00 4 02/01/2012 15:00 20:00 5 03/01/2012 10:00 18:00 6 04/01/2012 06:00 10:00 -------------------------------------------- Here for first and second record it should automatically detect as overlap. For third and fourth records, depending on users requirement, it may be detected as overlap or continuation. The column names for matching can be specified by user. It is specifically important to able to do it for datetime, date and interval datatypes. The same thing can be done through making table aliases using 2 aliases for same table but it would be better if same can be performed through informix functions.
create function is_time_overlap( start1 datetime hour to minute, end1datetime hour to minute, start2 datetime hour to minute, end2 datetime hour
to minute ) returning boolean;
if (end1 < start2) then
return 'f';
elsif (start1 > end2) then
return 'f';
else
return 't';
end if
end function;
Overload that function with others that support different precisions of
datetime and you are done. So, in your code you would be querying:
SELECT a.*, b.*
FROM some_table as a, some_table as b
WHERE a.date = b.date
AND is_time_overlap( a.start_time, a.end_time, b.start_time,
b.end_time )
...;
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Fri, Sep 7, 2012 at 6:05 AM, PRAJAKTA CHIKHALE
<prajakta_s_c@yahoo.co.in>wrote:
> Just recently I learned that there are built-in functions in sql server,
> oracle which detect overlap between two date / date-time ranges. When will
> the
> same be available for informix ids ?
>
> For e.g in some table if there are records as shown below.
> ============================================
> ID Date Start Time End Time
> --------------------------------------------
> 1 01/01/2012 10:00 12:00
> 2 01/01/2012 11:00 15:00
>
> 3 02/01/2012 10:00 15:00
> 4 02/01/2012 15:00 20:00
>
> 5 03/01/2012 10:00 18:00
>
> 6 04/01/2012 06:00 10:00
> --------------------------------------------
>
> Here for first and second record it should automatically detect as overlap.
> For third and fourth records, depending on users requirement, it may be
> detected as overlap or continuation. The column names for matching can be
> specified by user. It is specifically important to able to do it for
> datetime,
> date and interval datatypes.
>
> The same thing can be done through making table aliases using 2 aliases for
> same table but it would be better if same can be performed through informix
> functions.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--14dae93404af2a6cd304c91a1afa