Re: getting microseconds from oracle

From: Jurij Modic <jmodic_at_src.si>
Date: Sat, 30 Jan 1999 09:43:15 GMT
Message-ID: <36b2d3bb.7979242@news.arnes.si>

On Fri, 29 Jan 1999 16:04:30 -0600, Kent Eilers <kent.eilers_at_pca.state.mn.us> wrote:

>I need to debug an oracle procedure. I need to get subsecond times.
>the documentation on the 'to_char' function only gets me to 1/100 second
>intervals (to_char(sysdate, 'ssss.ss'). I need better than this.

In short, oracle's finest time granularity is 1 second. Using v$timer or sys.dbms_utility.get_time you can get time in hundredths of a second, but be avare this time is mesured from some arbitrary time in the past and is not directly tied with the "real" time returned by the sysdate. In other words, seconds mesured by the v$timer won't nececeraly change simultaneously with the seconds of the sysdate - there will be some constant offset. Here is a simple example of geting 100's of seconds from v$timer:

SYSTEM_at_PO73> select to_char(sysdate,'hh24:mi:ss') time,

  2                 hsecs from v$timer;

TIME                      HSECS
-------------------- ----------
09:58:56                 543960

SYSTEM_at_PO73> /

TIME                      HSECS
-------------------- ----------
09:58:58                 544142

SYSTEM_at_PO73> /

TIME                      HSECS
-------------------- ----------
09:58:59                 544294

BTW, your format mask doesn't show you 100ths of seconds, it merly prints the seconds of the current minute three times! Here is a proof:

SCOTT_at_PO73> select to_char(sysdate,'hh24:mi:ss') time,

  2                to_char(sysdate,'ssss.ss') useless from dual;

TIME                 USELESS
-------------------- --------------------
09:48:30             3030.30

SCOTT_at_PO73> /

TIME                 USELESS
-------------------- --------------------
09:48:34             3434.34

SCOTT_at_PO73> /

TIME                 USELESS
-------------------- --------------------
09:48:39             3939.39

Even if it is a typo in your message and you are using 5 's' for a format mask (eg 'sssss'), which shows you the seconds passed from the midnight, then the extra ".ss" part still shows you only the seconds passed from the begining of the current minute, not the 100's of the seconds!. Example of this one:

SCOTT_at_PO73> select to_char(sysdate,'hh24:mi:ss') time,
  2                to_char(sysdate,'sssss') secs_from_midnight,
  3                to_char(sysdate,'sssss.ss') useless from dual;

TIME                 SECS_FROM_MIDNIGHT   USELESS
-------------------- -------------------- --------------------
09:55:31             35731                35731.31

SCOTT_at_PO73> /

TIME                 SECS_FROM_MIDNIGHT   USELESS
-------------------- -------------------- --------------------
09:55:34             35734                35734.34

SCOTT_at_PO73> /

TIME                 SECS_FROM_MIDNIGHT   USELESS
-------------------- -------------------- --------------------
09:56:05             35765                35765.05

So there is no way you can obtain subseconds only by using format masks on dates!

>Any ideas?
>
>Thanks.

HTH,
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)

The above opinions are mine and do not represent any official standpoints of my employer Received on Sat Jan 30 1999 - 03:43:15 CST