使用oracle SQL查找日期范围内的星期几

假设下表结构：

Event: 
  id: integer
  start_date: datetime
  end_date: datetime

有没有办法查询一周中特定日期的所有事件？例如,我想找到一个查询,查找每个星期一的事件.弄清楚start_date或end_date是否属于星期一,但我不知道如何找出它们之间的日期.

纯SQL是首选,因为这里存在对存储过程的偏见,我们从Rails上下文中调用它,根据我的理解,它也不处理存储过程.

解决方法

SELECT  *
FROM    event
WHERE   EXISTS
        (
        SELECT  1
        FROM    dual
        WHERE   MOD(start_date - TO_DATE(1,'J') + level - 1,7) = 6
        CONNECT BY
                level <= end_date - start_date + 1
        )

子查询迭代从start_date到end_date的所有日期,检查每一天,如果它是星期一,则返回1.

您可以轻松地扩展此查询以适应更复杂的条件：检查事件是否发生在任何星期一或星期五13日,例如：

SELECT  *
FROM    event
WHERE   EXISTS  (
        SELECT  1
        FROM    dual
        WHERE   MOD(start_date - TO_DATE(1,7) = 6
                OR (MOD(start_date - TO_DATE(1,7) = 3 AND TO_CHAR(start_date + level - 1,'DD') = '13')
        CONNECT BY
                level <= end_date - start_date + 1
        )

请注意,我使用MOD(start_date – TO_DATE(1,’J’)级别 – 1,7)而不是TO_CHAR(‘D’).这是因为TO_CHAR(‘D’)受NLS_TERRITORY的影响,不应该用于检查某一天的某一天.

此查询不使用任何索引,并始终执行全表扫描.但在这种特定情况下这不是问题,因为给定间隔很可能包含星期一.

即使间隔为1天,如果间隔更长,则索引将返回14％的值,甚至更多.

由于INDEX SCAN在这种情况下效率低,并且内部子查询非常快(它使用内存中的FAST DUAL访问方法),我认为这将是一种最佳方法,无论是效率还是可扩展性.

有关详细信息,请参阅我博客中的条目：

> Checking event dates

（编辑：ASP站长网）