SQL oracle 查询两个日期之间的小时数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32006534/
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 query between two dates with hours
提问by K?τ?
This is my current oracle table:
这是我当前的 oracle 表：
- DATE = date
- HOUR = number
- RUN_DURATION = number
- 日期 = 日期
- 小时 = 数字
- RUN_DURATION = 数字
I need a query to get RUN_DURATION between two dates with hourslike
我需要一个查询来获取两个日期之间的 RUN_DURATION时间，例如
Select * from Datatable where DATE BETWEEN to_date('myStartDate', 'dd/mm/yyyy hh24:mi:ss') + HOURS? and to_date('myEndDate', 'dd/mm/yyyy hh24:mi:ss') + HOURS?
Select * from Datatable where DATE BETWEEN to_date('myStartDate', 'dd/mm/yyyy hh24:mi:ss') + HOURS? and to_date('myEndDate', 'dd/mm/yyyy hh24:mi:ss') + HOURS?
For example all data between 30.10.14 11:00:00 and 30.10.14 15:00:00
例如 30.10.14 11:00:00 和 30.10.14 15:00:00 之间的所有数据
I stuck to get the hours to the dates. I tried to add the hours into myStartDate but this will be ignored for the start date because of BETWEEN.
I know BETWEENshouldn't be used for dates but I can't try other opportunities because I don't know how to get DATEand HOURtogether...
Thanks!
我坚持要获得日期的时间。我尝试将小时数添加到 myStartDate 中，但由于BETWEEN. 我知道BETWEEN不应该被用于日期，但我不能尝试其他的机会，因为我不知道怎么去DATE和HOUR在一起...谢谢！
采纳答案by Lalit Kumar B
A DATEhas both date and time elements. To add hours to date, you just need to do some mathematics.
一个日期有日期和时间元素。要添加迄今为止的小时数，您只需要做一些数学运算。
For example,
例如，
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> SELECT SYSDATE, SYSDATE + 1/24 FROM dual;
SYSDATE SYSDATE+1/24
------------------- -------------------
2015-08-14 15:02:59 2015-08-14 16:02:59
Adds 1 hour to sysdate. So, if you have a column of date data type, just add number of hours to be added divided by 24because you add number of daysto a date. So, you just need to first convert the number of hours into date as hours/24.
将 1 小时添加到 sysdate。因此，如果您有一列日期数据类型，只需添加要添加的小时数除以 24，因为您将天数添加到日期。因此，您只需要首先将小时数转换为日期hours/24。
回答by Rapha?l Althaus
Well, you can add hour to your field date this way
好吧，您可以通过这种方式在您的现场日期中添加小时
select "DATE" + (hour / 24) from <yourTable>
this will give you ( from your first sample, may be different based on your format)
这会给你（来自你的第一个样本，根据你的格式可能会有所不同）
August, 14 2015 10:00:00
August, 14 2015 08:00:00
Based on that, you can do any between, select that you need.
基于此，您可以在两者之间进行任何操作，选择您需要的。
In your case
在你的情况下
where "DATE" + (hour / 24 )
which would make
这将使
Select *
from Datatable
where "DATE" + (hour / 24 )
BETWEEN to_date('30/10/2014 11:00:00', 'dd/mm/yyyy hh24:mi:ss') and
to_date('30/10/2014 15:00:00', 'dd/mm/yyyy hh24:mi:ss')
see SqlFiddle
(By the way, don't use reserved keywords for column name, but I guess this is just a sample).
（顺便说一句，不要对列名使用保留关键字，但我想这只是一个示例）。
回答by Praveen
If you want to add minto dateyou can use interval.
如果你想添加min到date你可以使用interval.
select sysdate, sysdate + interval '1' hour from dual
So,
所以，
Select *
from Datatable
where
DATE BETWEEN myStartDate + interval to_char(HOURS) hour
and myEndDate + interval to_char(HOURS) hour
回答by Arkadiusz ?ukasiewicz
SELECT *
FROM table
WHERE date BETWEEN start_date + NUMTODSINTERVAL( hour, 'HOUR' ) AND end_date + NUMTODSINTERVAL( hour, 'HOUR' )
