ORACLE date addition and subtraction

You can add or subtract oracle date from DATE or timestamp. The current date can be added to the year, month, day, hour, minute, second, operation of different time types, there are three methods: www.2cto.com 1 using the built-in function numtodsinterval to increase the hour, minute and second 2 add a simple number to increase the number of days 3 use the built-in function add_months to increase the number of years and months example: add an hour to the current date: SQL> select sysdate, sysdate + numtodsinterval (1, 'hour ') from dual; SYSDATE + NUMTODSINTE ------- 21:38:19 22:38:19 add 50 types of SQL to the current date> select sysdate, sysdate + numtodsinterval (50, 'minute ') from dual; SYSDATE + NUMTODSINTE ---------- ---- 21:39:12 22:29:12 add 45 seconds to the current date SQL> select sysdate, sysdate + numtodsinterval (45, 'second') from dual; SYSDATE + NUMTODSINTE ------- 21:40:06 21:40:51 add three days to the current date SQL> select sysdate, sysdate + 3 from dual; SYSDATE + 3 ------- 2010-10-14 21:40:46 2010-10-17 21:40:46 add 4 months to the current date SQL> select sysdate, add_months (sysdate, 4) from dual; SYSDATE ADD_MONTHS (SYSDATE, ------- 21:41:43 21:41:43 the current date is increased by 2 years SQL> select sysdate, add_months (sysdate, 12*2) from dual; SYSDATE ADD_MONTHS (SYSDATE, ------- 21:42:17 21:42:17 the timestamp operation method is similar to the above; calculate the difference between two dates: for example, calculate the difference between 21:23:34 and the current time. SQL> select sysdate-to_date ('1970 21:23:34 ', 'yyyy-mm-dd hh24: mi: ss') dt fromdual; DT----1240.01623 if two dates are directly subtracted, the result is a data type. We may want to get the difference between the two dates in the form of ** year ** month ** day **:**: ** SQL> SELECT NUMTOYMINTERVAL (MONTHS_BETWEEN (DT1, DT2), 'month') mon, 2 numtodsinterval (dt1-(add_months (dt2, trunc (MONTHS_BETWEEN (DT1, DT2), 'day') DAY3 FROM (select sysdate DT1, 4 TO_DATE ('2017 21:23:34 ', 'yyyy-mm-dd hh24: mi: ss ') DT25 from dual) 6; mon day ------ -------- + 000000003-04 + 000000021 00:40:15. 999999999 means: 3 years, 4 months, 21 days, 00:40:15. 99999999