Both date and timestamp can be added and reduced.
There are three ways to manipulate different time types for the current date Gagnin, month, day, hour, minute, and second:
1 add hours, minutes and seconds using built-in functions Numtodsinterval
2 plus a simple number to increase the day
3 use built-in function add_months to increase year and month
Cases:
Add one hours to the current date:
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 the current date
Sql> Select Sysdate, Sysdate+numtodsinterval (' minute ') from dual;
Sysdate Sysdate+numtodsinte
——————- ——————-
2010-10-14 21:39:12 2010-10-14 22:29:12
Add 45 seconds to the 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 the current date
Sql> Select Sysdate, sysdate+3 from dual;
Sysdate 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,
——————- ——————-
2010-10-14 21:41:43 2011-02-14 21:41:43
2 year increase in current date
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;
Ask for the difference of two dates:
Example: To find the difference between the 21:23:34 and the current time in 2007-5-23.
Sql> Select Sysdate-to_date (' 20070523 21:23:34′, ' yyyy-mm-dd hh24:mi:ss ') DT from
Dual;
Dt
———-
1240.01623
If the two date is directly subtracted, the result is a data type, we may want to get two date difference in the expression form:
* * * * * * * * * * * *:* *:* *
Sql> SELECT Numtoyminterval (Months_between (DT1, DT2), ' month ') Mon,
2 Numtodsinterval (dt1-(Add_months (Dt2,trunc (Months_between, DT1))
"Day") 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