Re: Milliseconds and DATE field

In article <3859603A.67D7A5CC_at_us.oracle.com>, Pete Sharman <psharman_at_us.oracle.com> wrote:
> This is in fact incorrect. The DATE datatype does NOT
> include milliseconds. This is a much requested enhancement,
> though. The only way I know of getting milliseconds is using
> the GET_TIME function. You would need to store the milliseconds
> as a separate number column, then concatenate them for user
> display.

Pete is correct. I wanted to mention, though, that the fine-granularity timer is /not/ synchronized with the system time. This can lead to problems.

Oracle's timer is accessible via the dbms_utility.get_time function, and also via the view V$TIMER. Both provide a quantity which measures the time in hundredths of a second from some arbitrary epoch. If you issue the query:

select to_char(sysdate,'HH24:MI:SS'), hsecs from v$timer;

many times quickly, you will observe that the sysdate second does not roll over when the HSECS value is evenly divisible by 1000. For example, I just obtained the following results on successive executions of the query:

TIME HSECS
---------- ---------

19:03:34     4644773
19:03:35     4644870
19:03:35     4644904
19:03:35     4644935
19:03:36     4644968
19:03:36     4645000
19:03:36     4645031

Observe that the HSECS value increases steadily (as expected), but the "00" hundredths of a second value does not occur at the same time that the SYSDATE second is incremented. Observe that the system timer seconds 19:03:35 and 19:03:36 are both found within V$TIMER second 46449, for example.

The main problem that you can experience is that if you assume that the system date and time, concatenated with the V$TIMER.HSECS value modulo 100, will steadily increase as time passes. Suppose, for instance, that with the above set of readings you were only capturing the last two digits of the HSECS column. Then you would have readings of:

BOGUS_TIME_VALUE

19:03:34.73
19:03:35.70
19:03:35.04
19:03:35.35
19:03:36.68
19:03:36.00
19:03:36.31

From these values, it appears that there are several instances of time travel, e.g. from 19:03:35.70 back to 19:03:35.04. If you are interested in precise timer values, you need to use either the sysdate function (with precision to the second) /or/ the v$timer value (with precision to the hundredth of a second), but not both.

Regards,

-bn

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Dec 16 1999 - 19:11:17 CST