SQL Oracle：仅按小时比较日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2343056/
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 :Compare dates only by the Hour
提问by Tom
We have a table which has a DATE column d.
我们有一个表，它有一个日期列 d。
I'd like to get all the rows where the d column is greater / lower than some value, regardless of the date.
无论日期如何，我都想获取 d 列大于/小于某个值的所有行。
For example
例如
| d |
-------------------
|2009/11/1 15:55:23|
--------------------
|2009/11/2 15:55:23|
--------------------
|2009/11/3 15:55:23|
--------------------
|2009/11/3 17:55:23|
--------------------
For example, If I want all the records marked after 5 PM:
例如，如果我想在下午 5 点之后标记所有记录：
select d
from my_table
where extract( hour from d ) > TO_DATE ('17:00:00','HH24:MI:SS')
This should return only one record
这应该只返回一条记录
|2009/11/3 17:55:23|
I don't know if this is the best way to do it, but I get an error on the extract function:
我不知道这是否是最好的方法，但我在提取功能上遇到错误：
ORA-30076: invalid extract field for extract source
Cause: The extract source does not contain the specified extract field.
Is there a better way to do this? Whats up with that error? extract only available for sysdate, as in all examples i've found?
有一个更好的方法吗？那个错误是怎么回事？提取仅适用于 sysdate，就像我发现的所有示例一样？
Thanks in advance
提前致谢
回答by exiter2000
How about this?
这个怎么样？
select d
from my_table
where to_char(d,'HH24') > '16';
回答by Bob Jarvis - Reinstate Monica
Don't have an Oracle database to test with at the moment but I think the following should work:
目前没有要测试的 Oracle 数据库，但我认为以下方法应该可行：
SELECT *
FROM MY_TABLE t
WHERE EXTRACT(HOUR FROM CAST(t.D AS TIMESTAMP)) > 16;
But I don't understand why the CAST would be needed as this pagesays that HOUR is a valid field in a DATE.
但我不明白为什么需要 CAST，因为此页面说 HOUR 是 DATE 中的有效字段。
Share and enjoy.
分享和享受。
回答by a'r
Try this:
尝试这个：
select d from my_table
where (d-trunc(d)) > 16/24;
