Re: Microseconds in Oracle

On Mon, 15 Jul 1996 17:41:10 -0400, Hirsch Malik <malikh_at_merck.com> wrote:

>Naren Chintala wrote:
>>
>> Vijay Vardhineni 604-1835 <vvardh01> wrote:
>> >This is a multi-part message in MIME format.
>> >
>> >---------------------------------189548178870758499620551264
>> >Content-Transfer-Encoding: 7bit
>> >Content-Type: text/plain; charset=us-ascii
>> >
>> >I tried to select milliseconds using TO_CHAR(sysdate,
>> >'yyyy-mm-dd-hh:mi:ss:mmmm') It is not working.
>> >
>> >for 'mmmm' I am getting the value of the month two times, not
>> >the milliseconds.
>> >
>> >vijay
>> >
>>
>> Vijay,
>>
>> This was posted in this newsgroup by someone from Oracle Corporation.
>>
>> select to_char(sysdate,'DD-MON-YYYY hh24:mi:ss:' ) || mod(hsecs,100)
>> from v$timer;
>>
>> Hope this helps;
>>
>> Naren Chintala
>> naren_at_mink.att.com
>
>I found that this query does not work as expected. For example, I
>ran this query successively within SQL*Plus:
>
> select hsecs, to_char(sysdate,'DD-MON-YYYY hh24:mi:ss:' ) ||
>mod(hsecs,100)
> from v$timer
>/
>
> HSECS
>----------
>TO_CHAR(SYSDATE,'DD-MON-YYYYHH24:MI:SS:')||MOD(HSECS,100)
>--------------------------------------------------------------------
>------------
> 22819316
>15-JUL-1996 17:23:49:16
>
>
>SQL> /
>
> HSECS
>----------
>TO_CHAR(SYSDATE,'DD-MON-YYYYHH24:MI:SS:')||MOD(HSECS,100)
>--------------------------------------------------------------------
>------------
> 22819341
>15-JUL-1996 17:23:49:41
>
>
>SQL> /
>
> HSECS
>----------
>TO_CHAR(SYSDATE,'DD-MON-YYYYHH24:MI:SS:')||MOD(HSECS,100)
>--------------------------------------------------------------------
>------------
> 22819366
>15-JUL-1996 17:23:49:66
>
>
>SQL> /
>
> HSECS
>----------
>TO_CHAR(SYSDATE,'DD-MON-YYYYHH24:MI:SS:')||MOD(HSECS,100)
>--------------------------------------------------------------------
>------------
> 22819391
>15-JUL-1996 17:23:50:91
>
>
>SQL> /
>
> HSECS
>----------
>TO_CHAR(SYSDATE,'DD-MON-YYYYHH24:MI:SS:')||MOD(HSECS,100)
>--------------------------------------------------------------------
>------------
> 22819414
>15-JUL-1996 17:23:50:14
>
>
>SQL> /
>
> HSECS
>----------
>TO_CHAR(SYSDATE,'DD-MON-YYYYHH24:MI:SS:')||MOD(HSECS,100)
>--------------------------------------------------------------------
>------------
> 22819435
>15-JUL-1996 17:23:50:35
>
>
>
>
>Notice that the second has changed at 17:23:50:91 even
>though only 25 milliseconds have elapsed since 17:23:49:66. It seems
>that SYSDATE is being updated before the v$timer.
>
>
> The contents of this message express only the sender's opinion.
> This message does not necessarily reflect the policy or views of
> my employer, Merck & Co., Inc. All responsibility for the statements
> made in this Usenet posting resides solely and completely with the
> sender.

Yes, This was pointed out by someone else to me. Since the v$timer is set according to some 'arbritray' epoch (and not according to the system clock) the above does not work since v$time and the system clock are not in sync. V$timer is only good for measuring elapsed times.

Sorry. (btw: the reason v$time gets out of sync is that it never resets itself when the system clock is changed, it is simply a counter)

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com -- Check out our web site! Brand new, uses Oracle Web Server and Database

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Tue Jul 16 1996 - 00:00:00 CEST