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

Source: Internet
Author: User
Tags date1 difference between two times

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"

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.