Oracle two-time subtraction __oracle

Oracle Two-time subtraction default is days

Oracle Two-time subtraction default is the number of days *24 to the difference in hours

Oracle Two-time subtraction the default is the number of days *24*60 to the difference in minutes

Oracle Two-time subtraction the default is the number of days *24*60*60 to the difference in seconds

--months_between (DATE2,DATE1)
Give out 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 Calculation time Difference expression

--Get a two-time difference in the number of Hao 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) The difference of Hao seconds from DUAL;
/*
The difference of Hao seconds
----------
86401000
1 row selected
*/

--Gets 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) of the difference in seconds from DUAL;
/*
Number of seconds difference
----------
86401
1 row selected
*/

--Get the difference minutes 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 in minutes from DUAL;
/*
Number of minutes difference
----------
1441
1 row selected
*/

--Get the difference hours 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) The difference of hours from DUAL;
/*
Hours of difference
----------
25
1 row selected
*/

--Get the difference days 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 '))--the difference in days from DUAL;
/*
Days of difference
----------
2
1 row selected
*/

----------------------------------------
Note: The number of days can be directly reduced by 2 dates, which is 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, 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