Common Oracle computing statements

Time Calculation of common Oracle computing statements: oracle two time subtraction by default is the number of days * 24 is the number of hours of difference oracle two time subtraction by default is the number of days * 24*60 is the difference in minutes oracle two the default value of time subtraction is the number of days * 24*60*60, which indicates the number of seconds. -- MONTHS_BETWEEN (date2, date1) Given the date2-date1 of the month SQL> select months_between ('19-December-111111', '19-March-100') mon_between from dual; MON_BETWEEN ----------- 9 SQL> select months_between (to_date ('2017. 05.20 ', 'yyyy. mm. dd'), to_date ('2017. 05.20 ', 'yyyy. dd ') mon_betw from dual; MON_BETW ----------60 Oracle Computing Time Difference Expression -- get the difference between two time Hao select ceil (To_date ('2017-05-02 00:00:00 ', 'yyyy-mm-dd hh24-mi-ss ')-To_date ('2017-04-30 23:59:59', 'yyyy-mm-dd hh24-mi-ss ') * 24*60*60*2008) difference in number of seconds from dual;/* difference in number of seconds ---------- 86401000 1 row selected */-- Obtain the difference in number of seconds between two times select ceil (To_date ('2017-05-02 00:00:00 ', 'yyyy-mm-dd hh24-mi-ss ')-To_date ('2017-04-30 23:59:59', 'yyyy-mm-dd hh24-mi-ss ') * 24*60*60) difference in seconds from dual;/* difference in seconds ---------- 86401 1 row selected */-- get the difference in minutes between two times select ceil (To_date ('2017-05-02 00:00:00 ', 'yyyy-mm-dd hh24-mi-ss ')-To_date ('2017-04-30 23:59:59', 'yyyy-mm-dd hh24-mi-ss ') * 24*60) difference minutes from dual;/* difference minutes ---------- 1441 1 row selected */-- get the difference hours of two time select ceil (To_date ('2017-05-02 00:00:00 ', 'yyyy-mm-dd hh24-mi-ss ')-To_date ('2017-04-30 23:59:59', 'yyyy-mm-dd hh24-mi-ss ') * 24) hours of variation from dual; /* hours of difference ---------- 25 1 row selected */-- obtain the number of different days of the two time select ceil (To_date ('2017-05-02 00:00:00 ', 'yyyy-mm-dd hh24-mi-ss ')-To_date ('1970-04-30 23:59:59', 'yyyy-mm-dd hh24-mi-ss ') days from dual; /* Days of difference ---------- 2 1 row selected */---------------------------------------- note: the number of days can be directly reduced by two days, this makes it more convenient ---------------------------------- -- Obtain the two-time month difference select (EXTRACT (year FROM to_date ('2017-05-01 ', 'yyyy-mm-dd ')) -EXTRACT (year FROM to_date ('1970-04-30 ', 'yyyy-mm-dd') * 12 + EXTRACT (month FROM to_date ('1970-05-01 ', 'yyyy-mm-dd')-EXTRACT (month FROM to_date ('2017-04-30 ', 'yyyy-mm-dd') months from dual; /* MONTHS ---------- 13 1 row selected */Optional Note: You can use the months_between function to make it easier to obtain the select EXTRACT (year FROM to_date ('2017-05-01 ', 'yyyy-mm-dd')-EXTRACT (year FROM to_date ('2017-04-30 ', 'yyyy-mm-dd') years from dual; /* YEARS ---------- 1 select sysdate, add_months (sysdate, 12) from dual; -- add 1 year select sysdate, add_months (sysdate, 1) from dual; -- add January select sysdate, TO_CHAR (sysdate + 7, 'yyyy-mm-dd HH24: MI: ss') from dual; -- add 1 week select sysdate, TO_CHAR (sysdate + 1, 'yyyy-mm-dd HH24: MI: ss') from dual; -- add 1-day select sysdate, TO_CHAR (sysdate + 1/24, 'yyyy-mm-dd HH24: MI: SS ') from dual; -- add 1 hour select sysdate, TO_CHAR (sysdate + 1/24/60, 'yyyy-mm-dd HH23: MI: ss') from dual; -- add 1 minute select sysdate, TO_CHAR (sysdate + 1/24/60/60, 'yyyy-mm-dd HH23: MI: ss') from dual; -- add 1 second select sysdate + 7 from dual; -- add 7 days Oracle retain two select trunc (CUR_SUM, 2) from data_record after the decimal point; convert decimals into percentages => round (zcbj/zs * 100) | '%' = trunc (zcbj/zs), 2) * 100 | '%'