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

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/836735/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-18 18:11:26  来源:igfitidea点击:

Finding the days of the week within a date range using oracle SQL

sqloracle

提问by cpm

Suppose the following table structure:

假设如下表结构:

Event: 
  id: integer
  start_date: datetime
  end_date: datetime

Is there a way to query all of the events that fall on a particular day of the week? For example, I would like to find a query that would find every event that falls on a Monday. Figuring out if the start_dateor end_datefalls on a Monday, but I'm not sure how to find out for the dates between.

有没有办法查询发生在一周中某一天的所有事件?例如,我想找到一个查询来查找发生在星期一的每个事件。确定start_date或是否在end_date星期一,但我不确定如何找出两者之间的日期。

Pure SQL is preferred since there is a bias against stored procedures here, and we're calling this from a Rails context which from what I understand does not handle stored procedures as well.

纯 SQL 是首选,因为这里对存储过程有偏见,我们从 Rails 上下文中调用它,据我所知,它也不能处理存储过程。

回答by Quassnoi

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
        )

The subquery iterates all days from start_dateto end_date, checks each day, and if it's a Monday, returns 1.

子查询从start_dateto 开始迭代所有天end_date,每天检查一次,如果是 a Monday,则返回1

You can easily extend this query for more complex conditions: check whether an event falls on ANY Monday OR Friday 13th, for instance:

您可以针对更复杂的条件轻松扩展此查询:检查事件是否落在 上ANY Monday OR Friday 13th,例如:

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

Note that I use MOD(start_date - TO_DATE(1, 'J') + level - 1, 7)instead of TO_CHAR('D'). This is because TO_CHAR('D')is affected by NLS_TERRITORYand should not be used for checking for a certain day of week.

请注意,我使用MOD(start_date - TO_DATE(1, 'J') + level - 1, 7)代替TO_CHAR('D'). 这是因为TO_CHAR('D')NLS_TERRITORY一周中某一天的影响,不应用于检查。

This query does not use any indexes and always performs a full table scan. But this is not an issue in this specific case, as it's highly probable that a given interval will contain a Monday.

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

Even if the intervals are 1day long, the index will return 14%of values, if intervals are longer, even more.

即使间隔是1一天,索引也会返回14%值,如果间隔更长,甚至更多。

Since INDEX SCANwould be inefficient in this case, and the inner subquery is very fast (it uses in-memory FAST DUALaccess method), this, I think, will be an optimal method, both by efficiency and extensibility.

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

See the entry in my blog for more detail:

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

回答by JosephStyons

This should do it more simply:

这应该更简单:

select *
from event
where 2 between to_number(trim(to_char(start_date,'D')))
            and to_number(trim(to_char(end_date,'D')))
   or (end_date - start_date) > 6