Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

oracle date to millisecond level

prasad_15
Advisor

oracle date to millisecond level

Hi,

Does any body have any utility in java or C which can extract oracle date and display in millisecond level,

Thanks in advance
7 REPLIES
James A. Donovan
Honored Contributor

Re: oracle date to millisecond level

Take a look at Metalink Note #148742.1

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=148742.1

Problem Description

-------------------

Sometimes it is needed to retrive the system time down to a resolution of milliseconds.

Oracle does not provide this. The highest resolution which can be handled by DATE is 1 second.

The Package DBMS_UTILITY provides a Function GET_TIME which returns the number of 1/100 seconds since an arbitrary point of start. This point of start cannot be changed from outside.
Remember, wherever you go, there you are...
Jeff Schussele
Honored Contributor

Re: oracle date to millisecond level

Hi Prasad,

HP-UX has the
gettimeofday()
function that gets down to microsecond granularity.

man gettimeofday for details.

Rgds,
Jeff
PERSEVERANCE -- Remember, whatever does not kill you only makes you stronger!
Yogeeraj_1
Honored Contributor

Re: oracle date to millisecond level

hi,

try:
YD@MYDB.MU> CREATE or replace JAVA SOURCE
2 NAMED "YdTimestamp"
3 AS
4 import java.lang.String;
5 import java.sql.Timestamp;
6
7 public class YdTimestamp
8 {
9 public static String getTimestamp()
10 {
11 return (new
12 Timestamp(System.currentTimeMillis())).toString();
13 }
14* };
YD@MYDB.MU> /

Java created.

YD@MYDB.MU> create or replace function my_timestamp return varchar2
2 AS LANGUAGE JAVA
3 NAME 'YdTimestamp.getTimestamp() return java.lang.String';
4 /

Function created.

YD@MYDB.MU> select my_timestamp, to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

MY_TIMESTAMP
--------------------------------------------------------------------------------
TO_CHAR(SYSDATE,'YY
-------------------
2004-03-23 05:33:41.062
2004-03-23 09:33:32


YD@MYDB.MU>

hope this helps!
regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: oracle date to millisecond level

hi again,

sorry for the garbled output.

attaching a more readable version.

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
prasad_15
Advisor

Re: oracle date to millisecond level

Hi Yogeeraj,

I need a little modification to this . like if i have a table with a date field . I want to know in millisecond the data was stored.
while retrieving . something like
select my_timstamp(work_dte) from dual;

please advise.

regards
Prasad
Bill Hassell
Honored Contributor

Re: oracle date to millisecond level

Since it takes dozens of milliseconds to complete a row insertion, having millisecond accruracy seems like a false level of accuracy. Depending on buffering, current I/O queues and many other kernel tasks, the actual time that data was written to the disk could be +/- 1000ms. Even after the data is written, there will variable delays in getting back to the Oracle application and then to your code to request the time in milliseconds.


Bill Hassell, sysadmin
Yogeeraj_1
Honored Contributor

Re: oracle date to millisecond level

hi,

Unfortunately, the SQL92 date/time elements provides this functionality (this feature did not make it into versions lesser than 9i - the granularity of time is down to the second only).

With old versions of Oracle, you can use Java stored procedures or C external procedures to get time at a finer level of detail but you will have to store it in a number field yourself -- it cannot be saved in an Oracle DATE field.

In Oracle 9i, you have the timestamp datatype.
hence there you can do the following:
SQL> create table t ( col1 timestamp);

Table created.

SQL> insert into t select sysdate from dual;

1 row created.

SQL> commit;

Commit complete.

SQL> select col1 from t;

COL1
---------------------------------------------------------------------------
24-MAR-04 08.25.29.000000 AM

SQL>


hope this helps!
regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)