Date Time Format and milliseconds
Moonstone
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
Rolf Hauck
get them by playing around with NLS_DATE_FORMAT. Resolution of date ist
seconds. :-((
Rolf
Thomas Kyte
(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
tk...@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.
Winnie Liu
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
Thomas Kyte wrote in message <362b2f4...@192.86.155.100>...
Thomas Kyte
(if that email address didn't require changing)
>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;
end;
/
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...
na...@hotmail.com
field (like MILLISECONDS NUMBER(3,0)).
If you want Oracle to tell you milliseconds then you can use the following
function below (included in %ORACLE_HOME%/rdbms80/admin/DBMSUTIL.SQL):
function get_time return number;
-- Find out the current time in 100th's of a second.
-- Output argukments:
-- get_time
-- The time is the number of 100th's of a second from some
-- arbitrary epoch.
I think you can only use this to compare to points in time and find out the
millisecond difference, but you may find other uses...
-Frank
>
> Rolf
>
> Moonstone 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
>
-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
slni...@my-dejanews.com
own field. C counts time in milliseconds (I believe... it's been a while). If
you get the time (with milliseconds), parse it to time, and milliseconds and
store it into two fields, you could concat them when selecting from the table.
S Niemann
> >>A copy of this was sent to "Moonstone" <Moon...@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
> >>>
> >>>
> >>
-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
Christophe Nicolas
dynamic view V$TIMER or the DBMS_UTILITY.GET_TIME function. This is the
better time precision Oracle can store.