470,873 Members | 1,879 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,873 developers. It's quick & easy.

10g timestamp

ts
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


Jul 19 '05 #1
1 11979
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" <to******@cox.net> wrote in message
news:Eiu4d.289102$Lj.237847@fed1read03...
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

Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by rong.guo | last post: by
5 posts views Thread by Prabu Subroto | last post: by
7 posts views Thread by JJ | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.