oracle 从日期时间减去一小时而不是一天
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16084484/
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
Subtract one hour from datetime rather than one day
提问by lightweight
I have a datetimecolumn in Oracle (MM/DD/YYYY HH:MM:SS AM/PM) but when I do this:
我datetime在 Oracle 中有一个列（MM/DD/YYYY HH:MM:SS AM/PM）但是当我这样做时：
SELECT MAX(D_DTM)-1 FROM tbl1
...it goes back a day. How do I remove one hour from the column rather than one day?
......它可以追溯到一天。如何从列中删除一小时而不是一天？
I've also noticed that the datetimerecords for 12AM look like MM/DD/YYYYand not MM/DD/YYYY 00:00:00; I'm not sure if that matters.
我还注意到datetime12AM的记录看起来像MM/DD/YYYY而不是MM/DD/YYYY 00:00:00；我不确定这是否重要。
回答by A.B.Cade
Randy's answeris good, but you can also use intervals:
Randy 的回答很好，但您也可以使用间隔：
SELECT MAX(D_DTM)- interval '1' hour FROM tbl1
回答by Randy
yes - dates go by integer days.
是 - 日期按整数天计算。
if you want hours you need to do some math - like -(1/24)
如果你想要几个小时，你需要做一些数学 - 比如 -(1/24)
回答by Ed Gibbs
Or use the INTERVALfunction. It has the same result but I think it reads more clearly - that's of course just an opinion :)
或者使用INTERVAL函数。它有相同的结果，但我认为它读起来更清楚——这当然只是一个意见:)
SELECT MAX(D_DTM) - INTERVAL '1' HOUR FROM tbl1
The nice thing about the INTERVALfunction is that you can make the interval be years, months, days, hours, minutes or seconds when dealing with a DATEvalue, though the month interval can be tricky when dealing with end-of-month dates.
该INTERVAL函数的好处在于，您可以在处理DATE值时将间隔设置为年、月、日、小时、分钟或秒，但在处理月末日期时，月间隔可能会很棘手。
And yes, the quote around the 1in the example is required.
是的，1示例中的引号是必需的。
You can also use the Oracle-specific NumToDSIntervalfunction, which is less standard but more flexible because it accepts variables instead of constants:
您还可以使用特定于 Oracle 的NumToDSInterval函数，该函数不太标准但更灵活，因为它接受变量而不是常量：
SELECT MAX(D_DTM) - NUMTODSINTERVAL(1, 'HOUR') FROM tbl1
回答by sakumar
select sysdate - numtodsinterval(1,'hour') from dual
回答by Ankit Kachchhi
Its simple.
这很简单。
sysdate - 5/(24*60*60) --> Subtracts 5 seconds from systime
sysdate - 5/(24*60*60) --> 从系统时间中减去 5 秒
sysdate - 5/(24*60) --> Subtracts 5 minutes from systime
sysdate - 5/(24*60) --> 从系统时间中减去 5 分钟
sysdate - 5/(24) --> Subtracts 5 hours from systime
sysdate - 5/(24) --> 从 systime 中减去 5 小时
Hence
因此
select (sysdate - (1/24)) from dual
select (sysdate - (1/24)) from dual
回答by Joe W
Another method of using intervals is
另一种使用间隔的方法是
NUMTODSINTERVAL( number, expression )
examples
例子
NUMTODSINTERVAL(150, 'DAY')
NUMTODSINTERVAL(1500, 'HOUR')
NUMTODSINTERVAL(15000, 'MINUTE')
NUMTODSINTERVAL(150000, 'SECOND')
I bring this up because it is useful for situations where using INTERVAL wont work.
我提出这个是因为它在使用 INTERVAL 不起作用的情况下很有用。
