In 10g why can I not get a 6 digit millisecond timestamp. Here is my Sql.

Select Current_Timestamp(6) from dual;


The milliseconds always looks like .931000.

-thanks

Hello.

It is known problem of Oracle DB on Wintel (Windows and/or Intel) platoform.
Mainly it is because of bad timer resolution in Windows(NT) on Intel
platform - 1/100 of second (10 milliseconds).
We faced with the same problem with Oracle 9i. We solved it just by creating
special stored function - GetSysTimeStamp.
We use simple counter. I mean - seqnece from 0 to 999 to provide additional
digits (the similar way is used in IBM DB2 on Wintel platform).
The example:

create or replace function GetSysTimeStamp return timestamp
is fraction_str varchar(50);
time_str varchar(50);
seq_nectval number;
begin
select XXX_FRACTION_CTR.nextval
into seq_nectval
from dual;
time_str:=to_char(systimestamp,'YYYY-MM-DD-HH24.MI.SSX');

fraction_str:=to_char(to_number(substr(to_char(sys timestamp,'ff'),0,3))*1000
+ seq_nectval);
return to_timestamp(time_str||fraction_str, 'YYYY-MM-DD-HH24.MI.SSXFF');
end;

Then we replaced SYSTIMESTAMP with our GetSysTimeStamp.
The results looks like:

..931000
..931001
..931002
..931003
..942000
..942001
..942002
etc.

If you find other solutioins please let me know (please write me to dima_ben
@ ukr.net).

WBR,
Dmitry.


"ts" <tomstack (AT) cox (DOT) net> wrote