Oracle Time Difference Calculation

Two date Type fields: Start_date,end_date, which calculates the time difference between these two dates (in days, hours, minutes, seconds, milliseconds, respectively):

Days:

ROUND (To_number (end_date-start_date))

Hours:

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)

Milliseconds:

ROUND (To_number (end_date-start_date) * 24 * 60 * 60 * 1000)

Oracle Compute time difference function 2008-08-20 10:2 Date Type fields: Start_date,end_date, which calculates the time difference between these two dates (respectively, in days, hours, minutes, seconds, milliseconds): Days: ROUND (To_number (end_ date-start_date)) Hours: 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) MS: ROUND (To_number (end_date-start_date) * 24 * 60 * 60 * 100 0)

Plus to_date and TO_CHAR functions:

In Oracle:
Select To_date (' 2007-06-28 19:51:20 ', ' yyyy-mm-dd HH24:mi:ss ') from dual;
In general sql:
Select To_date (' 2007-06-28 19:51:20 ', ' yyyy-mm-dd HH:mm:ss ') from dual;
Difference:
1, HH modified to HH24.
2, minutes of mm modified to MI.

The 24-hour format shows up with HH24.

Select To_char (sysdate, ' Yyyy-mm-dd HH24:mi:ss ') from dual;

Select To_date (' 2005-01-01 13:14:20 ', ' yyyy-mm-dd HH24:mi:ss ') from dual;

To_date () function

1. Date Format parameter meaning description

D the week of the week

Day's name, padded to 9 characters using a space

Day of DD Month

The day of the DDD year

DY-Day Shorthand name

The first week of the year in the IW ISO standard

IYYY ISO standard four-bit year

YYYY four-bit year

The last three digits of the yyy,yy,y year, two bits, one

HH hours, in 12-hour meter

HH24 hours, by 24 hours

MI points

SS sec

MM Month

Shorthand for Mon Month

Full name of month

W the first few weeks of the month

The first few weeks of the WW year 1. Date-time interval operation

Time minus 7 minutes for the current time

Select Sysdate,sysdate-interval ' 7 ' MINUTE from dual

The current time minus 7 hours of time

Select Sysdate-interval ' 7 ' hour from dual

The current time minus 7 days

Select Sysdate-interval ' 7 ' Day from dual

Time minus July for current time

Select Sysdate,sysdate-interval ' 7 ' month from dual

Time minus 7 years in the current time

Select Sysdate,sysdate-interval ' 7 ' year from dual

Time interval multiplied by a number

Select Sysdate,sysdate-8 *interval ' 2 ' hour from dual

2. Date-to-character manipulation

Select Sysdate,to_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual

Select Sysdate,to_char (sysdate, ' Yyyy-mm-dd hh:mi:ss ') from dual

Select Sysdate,to_char (sysdate, ' yyyy-ddd hh:mi:ss ') from dual

Select Sysdate,to_char (sysdate, ' yyyy-mm iw-d hh:mi:ss ') from dual

Refer to the related documentation for Oracle (ORACLE901DOC/SERVER.901/A90125/SQL_ELEMENTS4. htm#48515)

3. Character to date operation

Select To_date (' 2003-10-17 21:15:37 ', ' yyyy-mm-dd hh24:mi:ss ') from dual

The concrete usage is similar to the to_char above.

4.to_number
Convert characters to numbers using the To_number function
To_number (char[, ' format ')

Number format format
9 represents a number
0 Force Display 0
$ place a $ character
L Place a floating local currency symbol
. Show decimal point
, displays the thousand indicator

The meaning of the to_date parameter in Oracle

1. Date Format parameter meaning description
D the week of the week
Day's name, padded to 9 characters using a space
Day of DD Month
The day of the DDD year
DY-Day Shorthand name
The first week of the year in the IW ISO standard
IYYY ISO standard four-bit year
YYYY four-bit year
The last three digits of the yyy,yy,y year, two bits, one
HH hours, in 12-hour meter
HH24 hours, by 24 hours
MI points
SS sec
MM Month
Shorthand for Mon Month
Full name of month
W the first few weeks of the month
The first few weeks of WW 1. Date-time interval operation
Time minus 7 minutes for the current time
Select Sysdate,sysdate-interval ' 7 ' MINUTE from dual
The current time minus 7 hours of time
Select Sysdate-interval ' 7 ' hour from dual
The current time minus 7 days
Select Sysdate-interval ' 7 ' Day from dual
Time minus July for current time
Select Sysdate,sysdate-interval ' 7 ' month from dual
Time minus 7 years in the current time
Select Sysdate,sysdate-interval ' 7 ' year from dual
Time interval multiplied by a number
Select Sysdate,sysdate-8 *interval ' 2 ' hour from dual
2. Date-to-character manipulation
Select Sysdate,to_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual
Select Sysdate,to_char (sysdate, ' Yyyy-mm-dd hh:mi:ss ') from dual
Select Sysdate,to_char (sysdate, ' yyyy-ddd hh:mi:ss ') from dual
Select Sysdate,to_char (sysdate, ' yyyy-mm iw-d hh:mi:ss ') from dual
Refer to the related documentation for Oracle (ORACLE901DOC/SERVER.901/A90125/SQL_ELEMENTS4. htm#48515)
3. Character to date operation
Select To_date (' 2003-10-17 21:15:37 ', ' yyyy-mm-dd hh24:mi:ss ') from dual
The concrete usage is similar to the to_char above.
4. Use of the Trunk/round function
Select Trunc (Sysdate, ' year ') from dual
Select Trunc (sysdate) from dual
Select To_char (trunc (sysdate, ' YYYY '), ' YYYY ') from dual
5.oracle data types with millisecond levels
--Returns the current time, month, day, hour, minute, millisecond
Select To_char (Current_timestamp (5), ' dd-mon-yyyy HH24:MI:SSxFF ') from dual;
--Returns the second millisecond of the current time, specifying the precision after seconds (Max =9)
Select To_char (Current_timestamp (9), ' Mi:ssxff ') from dual;
6. Calculate when the program is running (MS)
Declare
Type 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.. 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

Oracle Time Difference Calculation