jbiswas
asked on
How to handle milliseconds in Oracle
I have a Sybase database that we are migrating to Oracle. In Sybase we can store milliseconds in a datetime field. How can I do the same in Oracle. There must be a way to do this since I would assume there is a lot of scientific data which deals with milliseconds. Any help will be greatly appreciated.
ASKER
I know what you have written, and possibly that is the fact, but I was wondering how people store data for scientific applications in Oracle where milliseconds abound. There must be a way to do this!!!!!
You would have to store the data in a varchar2 field and convert it, or perhaps store to a second precision in a standard date field and store just the millisecond element in another number field. The application would then have to convert/interpret the data as appropriate.
Vlad.
Vlad.
Of Course, you could store the millisecond value , by inserting it yourself into a varchar2 field. But then, you are not relying on the database engine to handle and there could be synchronisation troubles, if you really need to handle time intervals that small.
I'm not certain, but the operating system might keep track of time down to the millisecond level. If this is the case, then you might be able to get the Operating System time and store that in the database, rather than Oracle's.
If you are using Oracle Forms to get the date, then you can use a USER_EXIT to execute a C function, or you can use the HOST command to execute a C program to get the time.
If you are using PL/SQL then you can use Oracle FFI (Foreign Function Interface) to get the time from the Operating System.
You could also use a Pro*C API to simultaneously get the current Operating System time and insert the record into the database.
If you are using Oracle Forms to get the date, then you can use a USER_EXIT to execute a C function, or you can use the HOST command to execute a C program to get the time.
If you are using PL/SQL then you can use Oracle FFI (Foreign Function Interface) to get the time from the Operating System.
You could also use a Pro*C API to simultaneously get the current Operating System time and insert the record into the database.
if i recall correctly, there is a function in dbms_util
package that returns the current timestamp. it returns it as
a NUMBER.
unfortunately, i dont have an oracle enviroment available right here, so i cant be more specific, but i KNOW this function exists.
package that returns the current timestamp. it returns it as
a NUMBER.
unfortunately, i dont have an oracle enviroment available right here, so i cant be more specific, but i KNOW this function exists.
ASKER
Yeah I have considered using a varchar field to store the data coming in, but how do you get the miliisecond timestamp after the data is transferred into Oracle. I mean every time a row is inserted or updated it is important to store the timestamp down to the millisecond level. If a package enables us to do that that would be really great.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
i checked.
there is a function - dbms_util.get_time which returns a number
indicating the time in 100'th of seconds.
you can use a trigger to update a field with the time down to
100th of seconds.
no function however returns miliseconds.
there is a function - dbms_util.get_time which returns a number
indicating the time in 100'th of seconds.
you can use a trigger to update a field with the time down to
100th of seconds.
no function however returns miliseconds.
There isn't a way to obtain the current time, down to the milisecond into a PL/SQL variable either.
Though I have seen some info about enhancement requests requesting this function to the time datatype. May be once they get that in, it would be available.
Hope this helps.