Both date and timestamp are available for addition and subtraction operations.
You can manipulate different time types for the current date Gagnin, month, day, hour, minute, and second, with three ways:
1 using built-in functions numtodsinterval add hours, minutes and seconds
2 Add a simple number to increase the day
3 using built-in function add_months to increase the year and month
Cases:
Increase the current date by one hours:
Sql> Select Sysdate, Sysdate+numtodsinterval (1, ' Hour ') from dual;
Sysdate Sysdate+numtodsinte
——————- ——————-
2010-10-14 21:38:19 2010-10-14 22:38:19
Add 50 minutes to current date
Sql> Select Sysdate, Sysdate+numtodsinterval (M, ' minute ') from dual;
Sysdate Sysdate+numtodsinte
——————- ——————-
2010-10-14 21:39:12 2010-10-14 22:29:12
Add 45 seconds to current date
Sql> Select Sysdate, Sysdate+numtodsinterval (' second ') from dual;
Sysdate Sysdate+numtodsinte
——————- ——————-
2010-10-14 21:40:06 2010-10-14 21:40:51
Add 3 days to current date
Sql> Select Sysdate, sysdate+3 from dual;
Sysdate sysdate+3
——————- ——————-
2010-10-14 21:40:46 2010-10-17 21:40:46
Increase the current date by 4 months
Sql> Select Sysdate, Add_months (sysdate,4) from dual;
Sysdate add_months (Sysdate,
——————- ——————-
2010-10-14 21:41:43 2011-02-14 21:41:43
Current date increased by 2 years
Sql> Select Sysdate, Add_months (sysdate,12*2) from dual;
Sysdate add_months (Sysdate,
——————- ——————-
2010-10-14 21:42:17 2012-10-14 21:42:17
The operation method of timestamp is similar to the above;
For the difference of two dates:
For example: To find the difference between the 2007-5-23 21:23:34 and the current time.
Sql> Select Sysdate-to_date (' 20070523 21:23:34′, ' yyyy-mm-dd hh24:mi:ss ') DT from
Dual;
Dt
———-
1240.01623
If two dates are subtracted directly, the resulting result is a data type, and we may want to get two date difference values in the form of:
Sql> SELECT Numtoyminterval (Months_between (DT1, DT2), ' month ') Mon,
2 Numtodsinterval (Add_months (Dt2,trunc months_between (DT1, DT2))
), ' Day '
3 from (SELECT sysdate DT1,
4 to_date (' 20070523 21:23:34′, ' yyyy-mm-dd hh24:mi:ss ') DT2
5 from DUAL)
6;
MON Day
—————- ———————-
+000000003-04 +000000021 00:40:15.999999999
namely: 3 years 4 months 21 days 00:40:15.99999999