Showing results for 
Search instead for 
Did you mean: 

Retrieve milliseconds

Go to solution
Andrew Luis Arruza
Frequent Advisor

Retrieve milliseconds

Our DBA's want to retrieve milliseconds for use in one of their queries.
Does anyone know of a % variable that would let them retrieve milliseconds.
Thanks for any/all help.
Points will definitely be assigned.
Thanks, andy
It is, after all, a matter of survival!!
Marco Paganini
Respected Contributor

Re: Retrieve milliseconds

Hello Andy,

I never knew of a way (in hpux/shell) of getting the current number of ms. However, if you're trying to measure performance, you could use the 'time' command that gives you the elapsed time for a command with millisecond accuracy.

Hope it helps,
Keeping alive, until I die.
A. Clay Stephenson
Acclaimed Contributor

Re: Retrieve milliseconds

I suppose about as close as you could come in the shell is this:

timex sqlplus scott/tiger myquery.sql

You would then need to create a baby sql command script which does everything the above script does EXCEPT the query.

timex sqlplus scott/tiger mydummy.sql. The difference between those should be close.
If it ain't broke, I can fix that.
Darrell Allen
Honored Contributor

Re: Retrieve milliseconds

Hi Andy,

I believe seconds is the lowest level of precision you can get from the date command. That would make sense because time is kept in the number of seconds since the epoch.

"What, Me Worry?" - Alfred E. Neuman (Mad Magazine)
A. Clay Stephenson
Acclaimed Contributor

Re: Retrieve milliseconds

Hi, I think I may have misread your question. If you truly want a high-resolution result within Oracle, about the only way I can think of in a Pro/C function that calls clock_gettime().
If it ain't broke, I can fix that.
Andrew Luis Arruza
Frequent Advisor

Re: Retrieve milliseconds

Doesn't Oracle (and Sybase) use the OS time to get the time in the get_time command they use?
It is, after all, a matter of survival!!
A. Clay Stephenson
Acclaimed Contributor

Re: Retrieve milliseconds

They use time() which return epoch seconds but the clock_gettime system call returns seconds + nanoseconds. (You are not guaranteed nanosecond resolution and in any event the call overhead would obliterate that accuracy but millisecond is possible.)
If it ain't broke, I can fix that.
Jonas Linden
Occasional Visitor

Re: Retrieve milliseconds

In Oracle you can use

select DBMS_UTILITY.GET_TIME from dual;

which gives 100ths of seconde that have elapsed since an arbitrary time.

Occasional Visitor

Re: Retrieve milliseconds


Oracle doen't support milliseconds.
But you can get 100th of a second by using the DBMS_UTILITY.GET_TIME package.

If you need milli seconds conversion, you need to write your own function to manipulate sysdate.

The following PL/SQL block is self explainatory ::

start_date date;
end_date date;
millisec number;
gt1 number;
gt2 number;


dbms_output.put_line('Get_Time Value Before :: '||gt1);
start_date:= sysdate;
dbms_output.put_line('Start Date :: '||to_char(start_date,'DD-MON-YYYY HH24:MI:SS'));

FOR raju IN 1..5000000
You can use dbms_lock.sleep(n) instead of this dump loop.

end_date:= sysdate;
dbms_output.put_line('End Date :: '||to_char(end_date,'DD-MON-YYYY HH24:MI:SS'));
millisec:= (end_date-start_date)*24*60*60*1000 ;
dbms_output.put_line('Get_Time Value After :: '||gt2);

dbms_output.put_line('Diff. in time in milli secs ::'||millisec);
dbms_output.put_line('Diff. in Get Time 100th of a sec ::'||to_char(gt2-gt1,'9999999'));

Good Luck !
Raju Joseph