Oracle time subtracts milliseconds, seconds, minutes, hours, days, and "go"

http://blog.csdn.net/redarmy_chen/article/details/7351410

Oracle Two time subtraction default is the number of days

Oracle two time subtraction The default is the number of days *24 is the difference between the hours

Oracle two time subtraction The default is the number of minutes *24*60 is the difference between

Oracle two time subtraction default is the number of days *24*60*60 is the difference in seconds

--months_between (DATE2,DATE1)
Give the month of date2-date1
Sql> Select Months_between (' 1 September-December-1999 ', ' 1 September-March -1999 ') Mon_between from dual;

Mon_between
-----------
9
Sql>select Months_between (to_date (' 2000.05.20 ', ' yyyy.mm.dd '), to_date (' 2005.05.20 ', ' yyyy.dd ')) MON_BETW from Dual

Mon_betw
---------
-60

Oracle COMPUTE time-Difference expressions

--Get a two-time difference in number of seconds
Select Ceil ((to_date (' 2008-05-02 00:00:00 ', ' yyyy-mm-dd hh24-mi-ss ')-to_date (' 2008-04-30 23:59:59 ', ' Yyyy-mm-dd hh24- Mi-ss ') * 24 * 60 * 60 * 1000) difference between Hao seconds from DUAL;
/*
Number of seconds of difference
----------
86401000
1 row selected
*/

--Get the difference in seconds between two times
Select Ceil ((to_date (' 2008-05-02 00:00:00 ', ' yyyy-mm-dd hh24-mi-ss ')-to_date (' 2008-04-30 23:59:59 ', ' Yyyy-mm-dd hh24- Mi-ss ') * 24 * 60 * 60) difference in seconds from DUAL;
/*
Number of seconds difference
----------
86401
1 row selected
*/

--Get the number of minutes of difference between two times
Select Ceil (((To_date (' 2008-05-02 00:00:00 ', ' yyyy-mm-dd hh24-mi-ss ')-to_date (' 2008-04-30 23:59:59 ', ' Yyyy-mm-dd hh24 -mi-ss ')) * 24 * 60) difference minutes from DUAL;
/*
Minutes of difference
----------
1441
1 row selected
*/

--Get the time difference between two hours
Select Ceil ((to_date (' 2008-05-02 00:00:00 ', ' yyyy-mm-dd hh24-mi-ss ')-to_date (' 2008-04-30 23:59:59 ', ' Yyyy-mm-dd hh24- Mi-ss ')) * 24) difference hours from DUAL;
/*
Hours of difference
----------
25
1 row selected
*/

--Get two time difference days
Select Ceil ((to_date (' 2008-05-02 00:00:00 ', ' yyyy-mm-dd hh24-mi-ss ')-to_date (' 2008-04-30 23:59:59 ', ' Yyyy-mm-dd hh24- Mi-ss ')) is a difference of days from DUAL;
/*
difference in days
----------
2
1 row selected
*/

----------------------------------------
Note: The number of days can be reduced directly by 2 dates, which makes it more convenient
----------------------------------------

--Get two-time month difference
Select (EXTRACT (to_date (' 2009-05-01 ', ' yyyy-mm-dd '))-EXTRACT (year from to_date (' 2008-04-30 ', ' yyyy-mm-dd ')) ) * 12 +
EXTRACT (Month from to_date (' 2008-05-01 ', ' yyyy-mm-dd '))-EXTRACT (Month from to_date (' 2008-04-30 ', ' yyyy-mm-dd ')) Months
from dual;
/*
MONTHS
----------
13
1 row selected
*/

--------------------------------------
Note: You can use the Months_between function to make it more convenient
--------------------------------------

--Get two-time year difference
Select EXTRACT (Year from to_date (' 2009-05-01 ', ' yyyy-mm-dd '))-EXTRACT (year from to_date (' 2008-04-30 ', ' yyyy-mm-dd ')) years from dual;
/*
Years
----------
1




Select Sysdate,add_months (sysdate,12) from dual; --plus 1 years
Select Sysdate,add_months (sysdate,1) from dual; --Add January
Select Sysdate,to_char (sysdate+7, ' Yyyy-mm-dd HH24:MI:SS ') from dual; --plus 1 weeks
Select Sysdate,to_char (sysdate+1, ' Yyyy-mm-dd HH24:MI:SS ') from dual; --plus 1 days
Select Sysdate,to_char (sysdate+1/24, ' Yyyy-mm-dd HH24:MI:SS ') from dual; --plus 1 hours
Select Sysdate,to_char (sysdate+1/24/60, ' Yyyy-mm-dd HH23:MI:SS ') from dual; --plus 1 minutes
Select Sysdate,to_char (sysdate+1/24/60/60, ' Yyyy-mm-dd HH23:MI:SS ') from dual; --plus 1 seconds
Select sysdate+7 from dual; --Plus 7 days

Oracle time subtracts milliseconds, seconds, minutes, hours, days, and "go"