Oracle Time Difference Calculation

Oracle Time Difference Calculation

Two Date fields: START_DATE, END_DATE, calculate the time difference between the two dates (by day, hour, minute, second, millisecond): Day: ROUND (TO_NUMBER (END_DATE-START_DATE )) hour: ROUND (TO_NUMBER (END_DATE-START_DATE) * 24) minute: ROUND (TO_NUMBER (END_DATE-START_DATE) * 24*60) Second: ROUND (TO_NUMBER (END_DATE-START_DATE) * 24*60*60) millisecond: ROUND (TO_NUMBER (END_DATE-START_DATE) * 24*60*60*1000) Two Date type fields: START_DATE, END_DATE: calculates the time difference between the two dates (by day, hour, minute, second, millisecond): Day: ROUND (TO_NUMBER (END_DATE-START_DATE) hour: ROUND (TO_NUMBER (END_DATE-START_DATE) * 24) minutes: ROUND (TO_NUMBER (END_DATE-START_DATE) * 24*60) seconds: ROUND (TO_NUMBER (END_DATE-START_DATE) * 24*60*60) millisecond: ROUND (TO_NUMBER (END_DATE-START_DATE) * 24*60*60*1000) plus the to_date and to_char functions: ORACLE: select to_date ('2017-06-28 19:51:20 ', 'yyyy-MM-dd HH24: mi: ss') from dual; In general SQL: select to_date ('2017-06-28 19:51:20 ', 'yyyy-MM-dd HH: mm: ss') from dual;

Differences:

View Code

1. Change HH to HH24. 2. Change mm in minutes to mi. The 24-hour format is displayed with HH24select to_char (sysdate, 'yyyy-MM-dd HH24: mi: ss') from dual; select to_date ('2017-01-01 13:14:20 ', 'yyyy-MM-dd HH24: mi: ss') from dual; to_date () function1. description of the parameter meaning of the date format D name of the DAY of the week, fill in a space to nine characters DD months in the day of the DDD year in the day of the DY day abbreviated name iw iso standard in the year of the week iyyy iso standard four-digit year YYYY 4 YYY, YY, the last three digits of Y years, two digits, one digit HH hour, HH24 hours in 12 hours, in 24 hours MI minutes SS seconds MM Month Mon Month abbreviated Month full name W the day of the Month in the WW year in the day of the week 1. date interval operation current time minus 7 minutes select sysdate, sysdate-interval '7' MINUTE from dual current time minus 7 hours select sysdate-interval '7' hour from dual current time minus 7 days select sysdate-interval '7' day from dual current time minus July TIME select sysdate, sysdate-interval '7' month from dual current time minus the time of 7 years select sysdate, sysdate-interval '7' year from dual time interval multiplied by a number select sysdate, sysdate-8 * interval '2' hour from dual

Date to character operations

Select sysdate, to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss') from dualselect sysdate, to_char (sysdate, 'yyyy-mm-dd hh: mi: ss ') from dualselect sysdate, to_char (sysdate, 'yyyy-ddd hh: mi: ss') from dualselect sysdate, to_char (sysdate, 'yyyy-mm iw-d hh: mi: ss') from dual refer to the relevant oracle documentation (ORACLE901DOC/SERVER.901/A90125/SQL _ELEMENTS4.HTM #48515)

Character-to-date operations

Select to_date ('1970-10-17 21:15:37 ', 'yyyy-mm-dd hh24: mi: ss') the specific usage of from dual is similar to that of to_char above.

TO_NUMBER

Use the TO_NUMBER function to convert characters to numbers TO_NUMBER (char [, 'format']) number Format 9 represents a number 0 forcibly display 0 $ place a $ character L place a floating local currency character. display the decimal point. The meaning of the to_date parameter in oracle is 1. description of the date format parameter D name of the DAY of the week, fill in a space to nine characters DD months in the day of the DDD year in the day of the DY day abbreviated name iw iso standard in the year of the week iyyy iso standard four-digit year YYYY 4 YYY, YY, the last three digits of Y years, two digits, one digit HH hour, HH24 hours in 12 hours, in 24 hours MI minutes SS seconds MM Month Mon Month abbreviated Month full name W the day of the Month in the WW year in the day of the week 1. date interval operation current time minus 7 minutes select sysdate, sysdate -Interval '7' MINUTE from dual current time minus 7 hours select sysdate-interval '7' hour from dual current time minus 7 days select sysdate-interval '7' day from dual current time minus July TIME select sysdate, sysdate-interval '7' month from dual current time minus the time of 7 years select sysdate, sysdate-interval '7' year from dual time interval multiplied by a number select sysdate, sysdate-8 * interval '2' hour from dual 2. select sysdate, to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss ') From dualselect sysdate, to_char (sysdate, 'yyyy-mm-dd hh: mi: ss') from dualselect sysdate, to_char (sysdate, 'yyyy-ddd hh: mi: ss ') from dualselect sysdate, to_char (sysdate, 'yyyy-mm iw-d hh: mi: ss ') from dual refer to the relevant oracle documentation (ORACLE901DOC/SERVER.901/A90125/SQL _ELEMENTS4.HTM #48515) 3. select to_date ('1970-10-17 21:15:37 ', 'yyyy-mm-dd hh24: mi: ss') for character-to-date operations. The specific usage of from dual is similar to that of to_char above. 4. use the trunk/ROUND function to select trunc (sysdate, 'Year') from dualselect trunc (sysdate) from dualselect to_char (trunc (sysdate, 'yyyy'), 'yyyy ') from dual 5. oracle has a data type in milliseconds -- returns the current time year month day hour minute second millisecond select to_char (current_timestamp (5), 'dd-MON-YYYY HH24: MI: SSxFF ') from dual; -- returns the second millisecond of the current time. You can specify the precision (max = 9) after the second select to_char (current_timestamp (9), 'mi: SSxFF ') from dual; 6. computing program running time (MS) declaretype rc is ref cursor; l_rc rc; l_dummy all_objects.object_name % type; l_start number default dbms_utility.get_time; begin for I in 1 .. 1000 loop open l_rc for 'select object_name from all_objects '| 'where object_id =' | I; fetch l_rc into l_dummy; close l_rc; end loop; dbms_output.put_line (round (dbms_utility.get_time-l_start) /100, 2) | 'Seconds... '); end;