Oracle 在 SQL 中添加 1 小时
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26510033/
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 Add 1 hour in SQL
提问by Travis
I am just trying to add 1 hour to a value, it is kind of complicated on where and why i am doing this but basically i just need to query something like this
我只是想为一个值增加 1 小时，关于我在哪里以及为什么这样做有点复杂，但基本上我只需要查询这样的东西
select DATE_ADD(hh,1,'2014-10-15 03:30:00 pm') from dual
I keep reading old articles that say to use dateAdd or date_add but I keep getting invalid identifier errors.
我一直在阅读说使用 dateAdd 或 date_add 的旧文章，但我不断收到无效标识符错误。
回答by Multisync
select sysdate + 1/24 from dual;
sysdateis a function without arguments which returns DATE type
+ 1/24adds 1 hour to a date
sysdate是一个没有参数的函数，它返回 DATE 类型
+ 1/24为日期增加 1 小时
select to_char(to_date('2014-10-15 03:30:00 pm', 'YYYY-MM-DD HH:MI:SS pm') + 1/24, 'YYYY-MM-DD HH:MI:SS pm') from dual;
回答by a_horse_with_no_name
Use an interval:
使用间隔：
select some_date_column + interval '1' hour
from your_table;
回答by Dmitriy
You can use INTERVAL type or just add calculated number value - "1" is equal "1 day".
您可以使用 INTERVAL 类型或只添加计算出的数值 - “1”等于“1天”。
first way:
第一种方式：
select date_column + INTERVAL '0 01:00:00' DAY TO SECOND from dual;
second way:
第二种方式：
select date_column + 1/24 from dual;
First way is more convenient when you need to add a complicated value - for example, "1 day 3 hours 25 minutes 49 seconds". See also: http://www.oracle-base.com/articles/misc/oracle-dates-timestamps-and-intervals.php
当您需要添加复杂的值时，第一种方法更方便 - 例如，“1 天 3 小时 25 分 49 秒”。另见：http: //www.oracle-base.com/articles/misc/oracle-dates-timestamps-and-intervals.php
Also you have to remember that oracle have two interval types - DAY TO SECOND and YEAR TO MONTH. As for me, one interval type would be better, but I hope people in oracle knows, what they do ;)
您还必须记住，oracle 有两种间隔类型 - DAY TO SECOND 和 YEAR TO MONTH。至于我，一种间隔类型会更好，但我希望 oracle 的人知道他们在做什么；)
回答by Bhargav Avancha
Old way:
旧方式：
SELECT DATE_COLUMN + 1 is adding a day
SELECT DATE_COLUMN + N /24 to add hour(s) - N being number of hours
SELECT DATE_COLUMN + N /1440 to add minute(s) - N being number of minutes
SELECT DATE_COLUMN + N /86400 to add second(s) - N being number of seconds
Using INTERVAL:
使用间隔：
SELECT DATE_COLUMN + INTERVAL 'N' HOUR or MINUTE or SECOND - N being a number of hours or minutes or seconds.
回答by BaSsGaz
To add/subtract from a DATE, you have 2 options :
要从 a 中添加/减去DATE，您有 2 个选项：
Method #1 :The easiest way is to use +and -to add/subtract days, hours, minutes, seconds, etc.. from a DATE, and ADD_MONTHS()function to add/subtract months and years from a DATE. Why ? That's because from days, you can get hours and any smaller unit (1 hour = 1/24 days), (1 minute = 1/1440 days), etc... But you cannot get months and years, as that depends on the month and year themselves, hence ADD_MONTHS()and noadd_years(), because from months, you can get years (1 year = 12 months).
方法#1：最简单的方法是使用+和-从 a 中添加/减去天、小时、分钟、秒等DATE，以及ADD_MONTHS()从DATE. 中添加/减去月和年的函数。为什么 ？那是因为从几天开始，您可以获得小时和任何更小的单位（1 小时 = 1/24 天）、（1 分钟 = 1/1440 天）等......但您无法获得月和年，因为这取决于年月自己，因此ADD_MONTHS()并没有add_years（），因为从几个月，就可以得到年（1年= 12个月）。
Let's try them :
让我们试试看：
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') FROM dual; -- prints current date: 19-OCT-2019 20:42:02
SELECT TO_CHAR((SYSDATE + 1/24), 'DD-MON-YYYY HH24:MI:SS') FROM dual; -- prints date + 1 hour: 19-OCT-2019 21:42:02
SELECT TO_CHAR((SYSDATE + 1/1440), 'DD-MON-YYYY HH24:MI:SS') FROM dual; -- prints date + 1 minute: 19-OCT-2019 20:43:02
SELECT TO_CHAR((SYSDATE + 1/86400), 'DD-MON-YYYY HH24:MI:SS') FROM dual; -- prints date + 1 second: 19-OCT-2019 20:42:03
-- Same goes for subtraction.
SELECT SYSDATE FROM dual; -- prints current date: 19-OCT-19
SELECT ADD_MONTHS(SYSDATE, 1) FROM dual; -- prints date + 1 month: 19-NOV-19
SELECT ADD_MONTHS(SYSDATE, 12) FROM dual; -- prints date + 1 year: 19-OCT-20
SELECT ADD_MONTHS(SYSDATE, -3) FROM dual; -- prints date - 3 months: 19-JUL-19
Method #2 :Using INTERVALs, you can or subtract an interval (duration) from a date easily. More than that, you can combine to add or subtract multiple units at once (e.g 5 hours and 6 minutes, etc..)
Examples :
方法#2：使用INTERVALs，您可以轻松地从日期中减去一个间隔（持续时间）。不仅如此，您还可以组合一次添加或减去多个单位（例如 5 小时 6 分钟等）。示例：
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') FROM dual; -- prints current date: 19-OCT-2019 21:34:15
SELECT TO_CHAR((SYSDATE + INTERVAL '1' HOUR), 'DD-MON-YYYY HH24:MI:SS') FROM dual; -- prints date + 1 hour: 19-OCT-2019 22:34:15
SELECT TO_CHAR((SYSDATE + INTERVAL '1' MINUTE), 'DD-MON-YYYY HH24:MI:SS') FROM dual; -- prints date + 1 minute: 19-OCT-2019 21:35:15
SELECT TO_CHAR((SYSDATE + INTERVAL '1' SECOND), 'DD-MON-YYYY HH24:MI:SS') FROM dual; -- prints date + 1 second: 19-OCT-2019 21:34:16
SELECT TO_CHAR((SYSDATE + INTERVAL '01:05:00' HOUR TO SECOND), 'DD-MON-YYYY HH24:MI:SS') FROM dual; -- prints date + 1 hour and 5 minutes: 19-OCT-2019 22:39:15
SELECT TO_CHAR((SYSDATE + INTERVAL '3 01' DAY TO HOUR), 'DD-MON-YYYY HH24:MI:SS') FROM dual; -- prints date + 3 days and 1 hour: 22-OCT-2019 22:34:15
SELECT TO_CHAR((SYSDATE - INTERVAL '10-3' YEAR TO MONTH), 'DD-MON-YYYY HH24:MI:SS') FROM dual; -- prints date - 10 years and 3 months: 19-JUL-2009 21:34:15
回答by Himanshu sharma
The calculation is simple
计算很简单
if you want to add 1 hour in the date .
如果您想在日期中添加 1 小时。
every day have 24 hour , you can add .
每天都有24小时，可以加。
select sysdate + 1/24 from dual;
if you want 1 day to add
如果您想添加 1 天
select sysdate + 24/24 from dual;
or
select sysdate + 1 from dual;
same as for 2, 3 , 4 day
For static date you have the answer below.
对于静态日期，您有以下答案。
