Home » SQL & PL/SQL » SQL & PL/SQL » How to get Milisecond from systimestamp?
How to get Milisecond from systimestamp? [message #269071] Thu, 20 September 2007 12:01 Go to next message
shall42
Messages: 10
Registered: October 2006
Junior Member
I have

select to_char(systimestamp) from dual;


20-SEP-07 11.54.04.583000 AM -05:00


Is there an easy way to get the ".583" from above?

I can get the MM-DD-YYY HH24.MI.SS

I think there should a way to get the mili seconds
from this function.

TIA
Steve


Re: How to get Milisecond from systimestamp? [message #269072 is a reply to message #269071] Thu, 20 September 2007 12:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I think there should a way to get the mili seconds from this function
I know there is a way. You need to apply a different format mask that includes milliseconds.
Re: How to get Milisecond from systimestamp? [message #269073 is a reply to message #269071] Thu, 20 September 2007 12:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
SQL> select systimestamp,
  2         1000*(seconds-trunc(seconds)) millisec
  3  from (select extract(second from systimestamp) seconds from dual)
  4  /
SYSTIMESTAMP                                                                  MILLISEC
--------------------------------------------------------------------------- ----------
20/09/2007 19:05:24.734 +02:00                                                     734

1 row selected.

But I wonder what is the purpose?

Regards
Michel
Re: How to get Milisecond from systimestamp? [message #269074 is a reply to message #269071] Thu, 20 September 2007 12:08 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
 select to_char(systimestamp,'FF3') from dual 


where 3 is your precision (I believe anything beyond 3 is all zeros).

[Updated on: Thu, 20 September 2007 12:43]

Report message to a moderator

Re: How to get Milisecond from systimestamp? [message #269213 is a reply to message #269074] Fri, 21 September 2007 03:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Just by curiosity I wanted to check performances between the solutions.
This gave me:
SQL> declare
  2    sttim pls_integer;
  3    run1  pls_integer;
  4    run2  pls_integer;
  5    ms    pls_integer;
  6  begin
  7    sttim := dbms_utility.get_time;
  8    for i in 1..1000000 loop
  9      select to_number(to_char(systimestamp,'FF3')) into ms from dual;
 10    end loop;
 11    run1 := dbms_utility.get_time - sttim;
 12    sttim := dbms_utility.get_time;
 13    for i in 1..1000000 loop
 14      select 1000*(seconds-trunc(seconds)) into ms 
 15      from (select extract(second from systimestamp) seconds from dual);
 16    end loop;
 17    run2 := dbms_utility.get_time - sttim;
 18    dbms_output.put_line ('Run1 ran in ' || run1 || ' hsecs');
 19    dbms_output.put_line ('Run2 ran in ' || run2 || ' hsecs');
 20    dbms_output.put_line ('Run1 ran in ' || round(run1/run2*100,2) || 
 21                          '% of Run2 time');
 22  end;
 23  /
Run1 ran in 4226 hsecs
Run2 ran in 4555 hsecs
Run1 ran in 92.78% of Run2 time

PL/SQL procedure successfully completed.

Mark solution is faster and the reason the calculation in second ("my") query. Oracle does a pretty good optimisation in the to_char date/timestamp function.
If you remove this calculation, then extract is faster (extracting seconds):
SQL> declare
  2    sttim pls_integer;
  3    run1  pls_integer;
  4    run2  pls_integer;
  5    secs  pls_integer;
  6  begin
  7    sttim := dbms_utility.get_time;
  8    for i in 1..1000000 loop
  9      select to_number(to_char(systimestamp,'SS.FF9')) into secs from dual;
 10    end loop;
 11    run1 := dbms_utility.get_time - sttim;
 12    sttim := dbms_utility.get_time;
 13    for i in 1..1000000 loop
 14      select extract(second from systimestamp) into secs from dual;
 15    end loop;
 16    run2 := dbms_utility.get_time - sttim;
 17    dbms_output.put_line ('Run1 ran in ' || run1 || ' hsecs');
 18    dbms_output.put_line ('Run2 ran in ' || run2 || ' hsecs');
 19    dbms_output.put_line ('Run1 ran in ' || round(run1/run2*100,2) || 
 20                          '% of Run2 time');
 21  end;
 22  /
Run1 ran in 4350 hsecs
Run2 ran in 4178 hsecs
Run1 ran in 104.12% of Run2 time

PL/SQL procedure successfully completed.

Regards
Michel
Re: How to get Milisecond from systimestamp? [message #269217 is a reply to message #269213] Fri, 21 September 2007 03:52 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Once you get this far by tuning, you know you are overdoing it Smile
Previous Topic: Sending PDF Files from Oracle 8i
Next Topic: ORA-00600: internal error code due to arguments
Goto Forum:
  


Current Time: Fri Feb 07 08:52:49 CST 2025