Oracle BI：选择上周的所有记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5720288/
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
Oracle BI: Select all records from last week
提问by Carlos Nunes-Ueno
I need to get all records with a date between Sunday and Saturday last week, inclusive, for whatever date the query is run. For today, April 19th 2011, that would be from April 10th to April 16th.
对于查询运行的任何日期，我都需要获取所有记录的日期在上周星期日和星期六之间（含）。对于今天，2011 年 4 月 19 日，那将是从 4 月 10 日到 4 月 16 日。
When I entered the dates manually and converted the filter to SQL, I got the following syntax:
当我手动输入日期并将过滤器转换为 SQL 时，我得到以下语法：
RESOLVED_DATE BETWEEN timestamp '2011-04-10 00:00:00' AND timestamp '2011-04-16 00:00:00'
I'm not even sure this is correct, because it seems to exclude dates on the 16th itself (shouldn't the time be 23:59:59?)
我什至不确定这是否正确，因为它似乎排除了 16 号本身的日期（时间不应该是 23:59:59 吗？）
回答by Ronnis
It is possible to determine the dates you want using combinations of next_dayand regular date arithmetic. Below code should be pretty close, but it's untested and probably fails on some corner case, but at least you get the general idea :)
可以使用next_day和常规日期算法的组合来确定您想要的日期。下面的代码应该非常接近，但它未经测试并且可能在某些极端情况下失败，但至少你得到了一般的想法:)
where resolved_date >= next_day( trunc(sysdate) - interval '14' day, 'SUN')
and resolved_date < next_day( trunc(sysdate) - interval '7' day, 'SUN')
trunc(sysdate)truncate the date to day; 2011-04-19 23:32:34 becomes 2011-04-19 00:00:00, i.e. removing the time component.
next_day(sysdate, 'SUN')returns the next sunday. If sysdate happens to be a sunday, the next sunday is returned.
Important: The day names have to be in the same language as your session.
The intervalthing is just a standard way of adding/subtracting different units of time from a date.
trunc(sysdate)将日期截断为一天；2011-04-19 23:32:34 变为 2011-04-19 00:00:00，即移除时间分量。
next_day(sysdate, 'SUN')下周日返回。如果 sysdate 恰好是星期日，则返回下一个星期日。
重要提示：日期名称必须与您的会话使用相同的语言。
这interval只是从日期中添加/减去不同时间单位的标准方法。
Putting it all together, the logic for the 19th of April 2011 would be:
综上所述，2011 年 4 月 19 日的逻辑是：
- Truncate sysdate => 2011-04-19 00:00:00
- subtract 14 days => 2011-04-05 00:00:00
- Find the next sunday => 2011-04-10 00:00:00
- 截断系统日期 => 2011-04-19 00:00:00
- 减去 14 天 => 2011-04-05 00:00:00
- 找到下一个星期天 => 2011-04-10 00:00:00
...and
...和
- Truncate sysdate => 2011-04-19 00:00:00
- subtract 7 days => 2011-04-12 00:00:00
- Find the next sunday => 2011-04-17 00:00:00
- 截断系统日期 => 2011-04-19 00:00:00
- 减去 7 天 => 2011-04-12 00:00:00
- 找到下一个星期天 => 2011-04-17 00:00:00
..resulting in the following query:
..导致以下查询：
where resolved_date >= timestamp '2011-04-10 00:00:00'
and resolved_date < timestamp '2011-04-17 00:00:00'
All resolved_dates that happened on or after the first second of the 10:th but before the first second of the 17:th would be included. Note that >=and <isn't equivalent to between.
将包括在 10:th 的第一秒或之后但在 17:th 的第一秒之前发生的所有已解决的日期。请注意，>=and<不等同于between.
A note on performance: I would make sure that Oracle correctly estimates the date range to be 7 days, and that the correct join order/method is used. If you expect the query to run for a while, you can afford to calculate the dates in the application and supply them as date litterals instead of computing them on the fly like I did above.
关于性能的说明：我会确保 Oracle 正确估计日期范围为 7 天，并且使用正确的连接顺序/方法。如果您希望查询运行一段时间，您可以计算应用程序中的日期并将它们作为日期文本提供，而不是像我上面那样即时计算它们。
回答by clarson
take a look at the to_date function: http://psoug.org/reference/builtin_functions.html
看看 to_date 函数：http://psoug.org/reference/builtin_functions.html
