关于时间跨度比较的OVERLAPS函数

Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4  

我曾经在我的博客http://hitc.blogdriver.com/hitc/1288452.html 里介绍了如何使用工作区管理器（workspace）来管理时态。但必须首先配置好工作区管理器。 由于blogdriver 空间有问题，所以计划把博客迁到ITPUB。

今天查看抓虾订阅的博客，看到这么Kevin Meade  的Undocumented OVERLAPS Function, Don't Use it Yet ，文中提到OVERLAPS函数，例如：

 

SQL>  SELECT *

  2     FROM dual

  3    WHERE (add_months(sysdate, -6), DATE '2008-08-08')

  4          OVERLAPS

  5          (sysdate - 180, interval '2' YEAR);

D

-

X

 

在Mimer SQL Reference Manual: 中对OVERLAPS进行了一些解释：

 

 

    The OVERLAPS predicate tests whether two “events” cover a common point in time or not, and has the form.:

 

    (expression, expression) OVERLAPS (expression, expression)

 

    Each of the two “events” specified on either side of the OVERLAPS keyword is a period of time between two specified points on the timeline. The two points can be specified as a pair of datetime values or as one datetime value and an INTERVAL offset.

 

    Each “event” is defined by a two expressions constituting a row value expression having two columns.

 

    The first column in each row value expression must be a DATE, TIME or TIMESTAMP and the value in the first column of the first “event” must be comparable.

 

    The second column in each row value expression may be either a DATE, TIME or TIMESTAMP that is comparable with the value in the first column or an INTERVAL with a precision that allows it to be added to the value in the first column.

 

    The value in the first column of each row value expression defines one of the points on the timeline for the event.

 

    If the value in the second column of the row value expression is a datetime, it defines the other point on the timeline for the event.

 

    If the value in the second column of the row value expression is an INTERVAL, the other point on the timeline for the event is defined by adding the values in the two column of the row value to expression together.

 

    The NULL value is assumed to be a point that is infinitely late in time.

 

    Either of the two points may be the earlier point in time.

 

    If the value in the first column of the row value expression is the NULL value, then this is assumed to be the later point in time.

 

从这里，我们可以看出OVERLAPS有两种用法：

除了本文开始提到的例子外，还有：

SQL> select * from dual

  2   where          (date '2007-01-01', interval '5' year)

  3         overlaps (date '2005-01-01', interval '10' year);

 

D

-

X

 

比较的只能是日期，而不是数字，例如：

SQL> select * from dual

  2       where          (1,5)

  3             overlaps (3,8) ;

     where          (1,5)

                     *

ERROR at line 2:

ORA-00932: inconsistent datatypes

 

由于这个函数是ORACLE的未公开的，所以不宜在产品系统中使用。一个Kevin Meade给出的替代方法：

where (s2 < e1 and e2 > s1) or (s1 < e2 and e1 > s2)

 

 

这个函数最早出现在David Aldridge’s的  post about OVERLAPS

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9036/viewspace-446865/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9036/viewspace-446865/