Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to handle milliseconds in Oracle

Posted on 1998-10-08
9
Medium Priority
?
14,284 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:jbiswas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 3

Expert Comment

by:junfeb
ID: 1081871
I don't think you can handle milliseconds in Oracle unfortunately.
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.
0
 
LVL 2

Author Comment

by:jbiswas
ID: 1081872
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!!!!!
0
 
LVL 3

Expert Comment

by:vlad_impala
ID: 1081873
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 3

Expert Comment

by:junfeb
ID: 1081874
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.
0
 
LVL 2

Expert Comment

by:dslavin
ID: 1081875
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.
0
 
LVL 1

Expert Comment

by:vulkanr
ID: 1081876
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.
0
 
LVL 2

Author Comment

by:jbiswas
ID: 1081877
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.
0
 
LVL 5

Accepted Solution

by:
Mujeeb082598 earned 400 total points
ID: 1081878
Hi :)

Why not consider using two fields (mydate date, mymillisec number(4)) in your table where the milliseconds are important, u have to do a bit of programming in your applications though.

Save the date as it is in the date field and save the milliseconds in another filed as number.

So when u do the update u update the date field separately and milliseonds field separately, and while displaying u can concatinate the two fileds.

Using two fields will save u from doing extra string manipulation.
0
 
LVL 1

Expert Comment

by:vulkanr
ID: 1081879
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.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question