Re: how to store time with milliseconds in Oracle ?

Hayden Worthington, Inc. <hayden_at_mindspring.com> wrote in article <35A70E3E.1606A479_at_mindspring.com>...
> Alain,
>
> > I have to store time HH:MM:SS + milliseconds in Oracle.
> > Is there an easy way to do this ? No date format seems to support
that.
>
> I dont have my reference book handy but I do know what we did on a
> previous project where we needed a 'millisecond' on the timestamp.
>
> We created a timestamp field and made use of the HSEC value from
> sys.v$timer. However, for public apps we had to grant them select
> priv's on the view for this to work.

You can also call dbms_utility.get_time to access v$timer.

[from Oracle7 Server Reference Manual, Dynamic Performance (V$) Tables]: V$TIMER This view lists the elapsed time in hundredths of seconds. Time is measured since the beginning of the epoch, which is operating system specific, and wraps around to 0 again whenever the value overflows four bytes (roughly 497 days).

This Column Datatype Represents This
HSECS NUMBER Elapsed time in hundredths of a second

I think that when you use the HSEC column from v$timer you should be aware that sysdate and hsec are 'out of synch' so the following could happen:

insert row 1 with timestamp '1998-07-11 09:58:59:12'
insert row 2 with timestamp '1998-07-11 09:59:00:77'
insert row 3 with timestamp '1998-07-11 09:59:00:05'

Row 3 now has an earlier timestamp as row 2, although it was inserted later. This came to happen because the sysdate-based seconds-part is not in synch with the hsec-based 'hundredths of a second'. Also, depending on your operating system/hardware the 'precision' may vary.

Gert
--
reply address is altered to keep the spam down remove the nospamplease part to reply... Gert Rijs
gem at wirehub.nl
www.wirehub.nl/~gem Received on Sat Jul 11 1998 - 04:06:58 CDT