使用 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
Finding the days of the week within a date range using oracle SQL
提问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_date
or end_date
falls 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_date
to end_date
, checks each day, and if it's a Monday
, returns 1
.
子查询从start_date
to 开始迭代所有天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_TERRITORY
and 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 1
day long, the index will return 14%
of values, if intervals are longer, even more.
即使间隔是1
一天,索引也会返回14%
值,如果间隔更长,甚至更多。
Since INDEX SCAN
would be inefficient in this case, and the inner subquery is very fast (it uses in-memory FAST DUAL
access 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