SQL 如何从 Oracle 中的日期中减去小时数，因此它也会影响当天
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30772434/
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 subtract hours from a date in Oracle so it affects the day also
提问by hi4ppl
I'm trying to subtract date from Oracle so it even effect the day as well. For example, if the timestamp is 01/June/2015 00 hours and if I subtract 2 hours, I want to be able to go to to 31/May/2014 22 hours.
我正在尝试从 Oracle 中减去日期，因此它甚至也会影响这一天。例如，如果时间戳是 01/June/2015 00 小时，如果我减去 2 小时，我希望能够转到 31/May/2014 22 小时。
I tried
我试过
to_char(sysdate-(2/11), 'MM-DD-YYYY HH24')
but it only subtracts the hour; it does not touch the day itself.
但它只减去小时；它不涉及这一天本身。
回答by a_horse_with_no_name
Others have commented on the (incorrect) use of 2/11to specify the desired interval.
其他人评论了（不正确）使用2/11来指定所需的间隔。
I personally however prefer writing things like that using ANSI intervalliterals which makes readingthe query much easier:
然而，我个人更喜欢使用 ANSIinterval文字编写类似的东西，这使得阅读查询更容易：
sysdate - interval '2' hour
It also has the advantage of being portable, many DBMS support this. Plus I don't have to fire up a calculator to find out how many hours the expression means - I'm pretty bad with mental arithmetics ;)
它还具有可移植的优点，许多 DBMS 都支持这一点。另外，我不必启动计算器来找出表达式意味着多少小时 - 我的心算很糟糕；）
回答by m3nation
Try this:
尝试这个：
SELECT to_char(sysdate - (2 / 24), 'MM-DD-YYYY HH24') FROM DUAL
To test it using a new date instance:
要使用新的日期实例对其进行测试：
SELECT to_char(TO_DATE('11/06/2015 00:00','dd/mm/yyyy HH24:MI') - (2 / 24), 'MM-DD-YYYY HH24:MI') FROM DUAL
Output is: 06-10-2015 22:00, which is the previous day.
输出为：06-10-2015 22:00，即前一天。
回答by Lalit Kumar B
sysdate-(2/11)
系统日期-(2/11)
A day consists of 24 hours. So, to subtract 2 hoursfrom a day you need to divide it by 24:
一天由24 hours. 因此，2 hours要从一天中减去，您需要将其除以24：
DATE_value - 2/24
Using intervalfor the same:
使用interval的是相同的：
DATE_value - interval '2' hour
回答by Tapan Pandya
date - nwill subtract n days form given date. In order to subtract hrs you need to convert it into day buy dividing it with 24. In your case it should be to_char(sysdate - (2 + 2/24), 'MM-DD-YYYY HH24'). This will subract 2 days and 2 hrs from sysdate.
date - n将从给定日期减去 n 天。为了减去小时，您需要将其转换为日买除以 24。在您的情况下，它应该是to_char(sysdate - (2 + 2/24), 'MM-DD-YYYY HH24')。这将从 sysdate 减去 2 天 2 小时。
回答by adel sameer
you should divide hours by 24 not 11
like this:select to_char(sysdate - 2/24, 'dd-mon-yyyy HH24') from dual
你应该
像这样除以 24 而不是 11 小时：select to_char(sysdate - 2/24, 'dd-mon-yyyy HH24') from dual
