| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Date Time Format and milliseconds
A copy of this was sent to "Winnie Liu" <oracle_dba_at_zdnetmail.com>
(if that email address didn't require changing)
On Mon, 19 Oct 1998 22:45:32 -0700, you wrote:
>Wait a second. There is an Oracle dynamic table which store the millisecond
>information. It's something like v$timer. (please do go and double check). I
>forget exactly what that values mean, please do lookup the documentation for
>that. The oracle date format will NOT store the millisecond information. But
>you can create another column and store the information in the v$timer into
>it.
>
>Winnie
>Certified DBA
>
right - there is a v$timer table (also available via the dbms_utility.get_time() function call).
It runs at a resolutino of hundreds of seconds...
It cannot be stored in the DATE field (original question)...
It *cannot* be used in conjunction with the DATE field -- it runs on a *different* schedule from the system clock. It is a simple timer that is started on its own schedule and is *not* in sync with the system clock. Consider this example:
begin
for i in 1 .. 5000 loop
dbms_output.put_line( to_char(sysdate,'hh24:mi:ss') || ' ' ||
to_char( mod(dbms_utility.get_time,100),'00'));
end loop;
09:44:04 31 09:44:04 31 09:44:04 31 09:44:04 31 09:44:04 31
09:44:04 68 09:44:04 68 09:44:04 68 09:44:05 69 09:44:05 69 09:44:05 69
09:44:05 99 09:44:05 99 09:44:05 99 09:44:05 00 09:44:05 00 09:44:05 00
See, the sysdate rolls from 4 seconds to 5 seconds but v$timer (same as dbms_utility.get_time) did not roll to zero as you would expect. Likewise, the v$timer value rolled from 99 to 0 but the seconds in sysdate did not change. You cannot pair these two values together as they beat to 2 different drummers...
>Thomas Kyte wrote in message <362b2f4b.827159_at_192.86.155.100>...
>>A copy of this was sent to "Moonstone" <Moonstone_at_nowhere.com>
>>(if that email address didn't require changing)
>>On Mon, 19 Oct 1998 09:54:19 +0200, you wrote:
>>
>>>Hi,
>>>is there a way to store milliseconds in a date field.
>>>I tried to play around with the NLS_DATE_FORMAT but I didn't succeed
>until
>>>now:
>>>Any sugesstions ?
>>>
>>>Cheers
>>>Eric
>>>
>>>
>>
>>No, the Oracle DATE datatype stores:
>>
>><quote src=server concepts manual>
>>The DATE datatype stores point–in–time values (dates and times) in a
>>table. The DATE datatype stores the year (including the century), the
>>month, the day, the hours, the minutes, and the seconds (after
>>midnight). Oracle can store dates ranging from Jan 1, 4712 BC through
>>Dec 31, 4712 AD. Unless you specifically specify BC, AD date entries
>>are the default.
>>
>>Oracle uses its own internal format to store dates. Date data is stored in
>>fixed–length fields of seven bytes each, corresponding to century, year,
>>month, day, hour, minute, and second.
>></quote>
>>
>>There is no space for milliseconds in there.
>>
>>Thomas Kyte
>>tkyte_at_us.oracle.com
>>Oracle Government
>>Herndon VA
>>
>>--
>>http://govt.us.oracle.com/ -- downloadable utilities
>>
>>---------------------------------------------------------------------------
>-
>>Opinions are mine and do not necessarily reflect those of Oracle
>Corporation
>>
>>Anti-Anti Spam Msg: if you want an answer emailed to you,
>>you have to make it easy to get email to you. Any bounced
>>email will be treated the same way i treat SPAM-- I delete it.
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Oct 20 1998 - 08:48:43 CDT
 |
 |