Overlapping Intervals (both numeric or date): Do they overlap? By how much?

Word has it that there is an undocumented "Overlaps" function in Oracle. The problem is, (after a few simple tests) it only works for date intervals and it only returns a true or false condition -- no actual values of how much overlap exists. Here's a simple example:

    
  select  case when  ( date'2012-02-25', date'2012-08-23' ) overlaps
                     ( date'2012-08-13', date'2012-11-13' ) 
               then 'It Overlaps'
               else 'nope'
          end as rslt
    from  dual ;

 So, being that it has very limited use, and that it is undocumented -- why even consider using it? Especially when there's a nice flexible supported construct that works just as well for numeric intervals as it does for date-time intervals.

Wasting no more time, then, here's the construct:

    
Given 2 intervals A and B (either numeric or date-time)in this form:

    A = ( A_bgn, A_end )
    B = ( B_bgn, B_end )

* If they overlap, the following will return a TRUE value; 
  if they don't overlap it will return FALSE.
  (This can be used in a WHERE clause or in a CASE statement.)
  
    >>>  least(A_end, B_end) > greatest(A_bgn, B_bgn)

* Similarly, this would test weather the intervals just touch:

    >>>  least(A_end, B_end) = greatest(A_bgn, B_bgn) 

* The following will give you the actual length of overlap. 
  For a date-time interval it will give you the number of days. 
  For a numeric interval it returns the numeric distance. 
  If the intervals don't overlap this will return a negative 
  value indicating how far apart are the endpoints.

    >>>  least(A_end, B_end) - greatest(A_bgn, B_bgn)

* A simple modification will return 0 if there is no overlap and 
  the length of the overlap if they actually do overlap.

    >>>  greatest(least(A_end, B_end) - greatest(A_bgn, B_bgn), 0)

So as a quick example, here are two queries that use the above constructs.
One for numeric intervals and one for date-time intervals.

    
   with
     w_data  as
       ( select  null  as A_bgn
              ,  null  as A_end
              ,  null  as B_bgn
              ,  null  as B_end
           from  dual  where 1=0
         --------------------------------------------
         union all  select  11, 13, 16, 20  from dual
         union all  select  12, 16, 14, 19  from dual
         union all  select  10, 25, 15, 20  from dual
         union all  select  12, 16, 16, 19  from dual
         --------------------------------------------
       )
   select  greatest ( least(A_end, B_end) - greatest(A_bgn, B_bgn), 0)  as intrvl_overlap
        ,             least(A_end, B_end) - greatest(A_bgn, B_bgn)      as diff
        ,  case when  least(A_end, B_end) > greatest(A_bgn, B_bgn)      then  'overlapping'  end  as reslt
        ,  case when  least(A_end, B_end) = greatest(A_bgn, B_bgn)      then  'touches'      end  as reslt2
     from  w_data ;

And ...

    
   with
     w_data  as
       ( select  null  as A_bgn
              ,  null  as A_end
              ,  null  as B_bgn
              ,  null  as B_end
           from  dual  where 1=0
         --------------------------------------------------------------------------------------------------------
         union all  select  to_date('0214~09:27','MMDD~HH24:MI'), to_date('0910~15:45','MMDD~HH24:MI')
                         ,  to_date('0923~09:45','MMDD~HH24:MI'), to_date('1008~11:07','MMDD~HH24:MI')  from dual
         union all  select  to_date('0214~09:27','MMDD~HH24:MI'), to_date('0825~15:45','MMDD~HH24:MI')
                         ,  to_date('0705~09:45','MMDD~HH24:MI'), to_date('1008~11:07','MMDD~HH24:MI')  from dual
         union all  select  to_date('0115~02:53','MMDD~HH24:MI'), to_date('1103~23:15','MMDD~HH24:MI')
                         ,  to_date('0305~03:10','MMDD~HH24:MI'), to_date('0312~15:10','MMDD~HH24:MI')  from dual
         union all  select  to_date('0411~08:53','MMDD~HH24:MI'), to_date('0416~13:30','MMDD~HH24:MI')
                         ,  to_date('0416~13:30','MMDD~HH24:MI'), to_date('0425~07:42','MMDD~HH24:MI')  from dual
         --------------------------------------------------------------------------------------------------------
       )
   select  greatest ( least(A_end, B_end) - greatest(A_bgn, B_bgn), 0)  as intrvl_overlap
        ,             least(A_end, B_end) - greatest(A_bgn, B_bgn)      as diff
        ,  case when  least(A_end, B_end) > greatest(A_bgn, B_bgn)      then  'overlapping'  end  as reslt
        ,  case when  least(A_end, B_end) = greatest(A_bgn, B_bgn)      then  'touches'      end  as reslt2
     from  w_data ;