SQL 如何仅根据 Oracle 中的日期部分比较两个 DATE 值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7433363/
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
How to compare two DATE values based only on date part in Oracle?
提问by Bhesh Gurung
I am trying to get counts for last 30 days with the following query -
我正在尝试使用以下查询获取过去 30 天的计数 -
SELECT date_occured, COUNT(*) FROM problem
WHERE date_occured >= (CURRENT_DATE - 30)
GROUP BY date_occured;
//date_occured field is of type DATE.
Basically, in my query I am trying to compare only the date part in the condition date_occured >= (CURRENT_DATE - 30), but it seems to compare the time too.
基本上，在我的查询中，我试图只比较条件中的日期部分date_occured >= (CURRENT_DATE - 30)，但它似乎也比较了时间。
I tried the TRUNC as follows -
我按如下方式尝试了 TRUNC -
TRUNC(date_occured) >= TRUNC(CURRENT_DATE - 30)
But when run the query it never returns.
但是当运行查询时它永远不会返回。
I also tried -
我也试过——
SELECT date_occured, COUNT(*) FROM problem
GROUP BY date_occured
HAVING TRUNC(date_occured) >= TRUNC(CURRENT_DATE - 30);
Again it never returns.
再一次，它永远不会回来。
How can I compare only the date parts from two DATE values in Oracle?
如何仅比较 Oracle 中两个 DATE 值的日期部分？
回答by Tony Andrews
For this condition you only need to TRUNC the right-hand side:
对于这种情况，您只需要 TRUNC 右侧：
WHERE date_occured >= TRUNC(CURRENT_DATE - 30)
Why? Because if TRUNC(date_occured) is later than TRUNC(CURRENT_DATE - 30), then any moment in time after TRUNC(date_occured) is bound to be later than TRUNC(CURRENT_DATE - 30) too.
为什么？因为如果 TRUNC(date_occured) 晚于 TRUNC(CURRENT_DATE - 30)，那么在 TRUNC(date_occured) 之后的任何时刻也必然晚于 TRUNC(CURRENT_DATE - 30)。
It is obviously always true that date_occured >= TRUNC(date_occured) (think about it).
date_occured >= TRUNC(date_occured) 显然总是正确的（想想看）。
Logic says that if A >= B and B >= C then it follows that A >= C
逻辑上说，如果 A >= B 并且 B >= C 那么它遵循 A >= C
Now substitute:
现在替换：
- A : date_occured
- B : TRUNC(date_occured)
- C : TRUNC(CURRENT_DATE - 30)
- A : date_occured
- B : TRUNC(date_occured)
- C : TRUNC(CURRENT_DATE - 30)
回答by Lost in Alabama
I think you'll want to trunc in the select part too:
我想你也想在选择部分截断：
SELECT TRUNC(date_occured) AS short_date_occured, COUNT(*)
FROM problem
WHERE date_occured >= trunc(SYSDATE- 30)
GROUP BY short_date_occured;
回答by Justin Cave
I would tend to suspect that Tony is correct and that you really only want to TRUNCthe right-hand side of the expression.
我倾向于怀疑 Tony 是正确的，并且您真的只想要TRUNC表达式的右侧。
If you do want to TRUNCboth sides of the expression and you're encountering performance issues, you probably need a function based index
如果您确实想要TRUNC表达式的两边并且遇到性能问题，您可能需要一个基于函数的索引
CREATE INDEX idx_problem_trunc_dt_occured
ON problem( trunc( date_occurred ) );
That would allow your original query to use the function-based index.
这将允许您的原始查询使用基于函数的索引。
回答by northpole
Try using SYSDATE vs CURRENT_DATE. Sysdate uses the server's local time where CURRENT_DATE returns current date/time for the server in the client's connection's local time.
尝试使用 SYSDATE 与 CURRENT_DATE。Sysdate 使用服务器的本地时间，其中 CURRENT_DATE 以客户端连接的本地时间返回服务器的当前日期/时间。
回答by Dervi? Kay?mba??o?lu
Here is the index friendly approach.
这是索引友好的方法。
you don't need to use functions on columns if you use Oracle's Native MONTHS_BETWEEN function.
如果您使用 Oracle 的本机 MONTHS_BETWEEN 函数，则不需要在列上使用函数。
It returns difference in number of months. Days are also given as difference but on the precision side that is why I preferred to use BETWEEN clause
它返回月数差异。天数也作为差异给出，但在精度方面，这就是为什么我更喜欢使用 BETWEEN 子句
WHERE MONTHS_BETWEEN(date_occured, CURRENT_DATE - 30) BETWEEN 0 AND 1
