Help.
We are not Oracle people here, we just need to interface our product with Oracle. A co-worker is having a devil of a time trying to store timestamps (i.e. down to fractions of seconds) on an Oracle database. Looking through the scanty documentation we have, plus web searches, he is none the wiser. Any advice would be appreciated.
I’m no Oracle expert, and it very much depends on the nature of your product, but…there’s plenty of Oracle info on the web. Try putting some or all of the following into a search engine:
Oracle, timestamp, sysdate, to_char, to_date
And here’s a syntax reference for Oracle SQL:
http://oradoc.photo.net/ora81/DOC/server.815/a67779/ch4e.htm
Oracle can easily store timestamps within its default DATE datatype, which is structured thus: ‘DD-MMM-YYYY hh:mm:ss’
**Example:
‘01-SEP-2001 12:00:00’**
meaning 12:00 noon on September 1st, 2001.
So whatever field (= column) is meant to store the timestamp, just specify that field as a regular ol’ DATE. The DATE datatype stores time info down to the minutes and second by default.
In SQL:
ALTER TABLE YOUR_TABLE
ADD [or MODIFY] (your_timestamp DATE);
Ticker, if the above does not speak to your particular problem, please post again in this thread.
Read this page for more detailed info:
http://www.arsdigita.com/books/sql/dates.html
Site home page: http://www.arsdigita.com/books/sql/
(That site is a very useful SQL primer written in laymen’s terms. Have your team review that site.)
I don’t know about Oracle’s timestamp data type, but I do know about SQL Server’s, and it sucks. Like you, we need to store date & time data down to the millisecond (specifically, we’re storing FILETIME’s). However, SQL Server’s Date/Time data type stores down to one three-hundreths of a second - not the same.
We ended up having to store the 8 byte filetimes as 8 byte integers in the database - interestingly enough, yet another data type SQL 7.0 doesn’t support (SQL 2K does). So we store it as a decimal data type. Talk about jumping through hoops!
Ticker, sorry – I hadn’t noticed that you needed precision down to fractions of a second. I may have misguided you – right out of the box, Oracle DATE datatype is fine for precision down to one second, but no lower.
I know of no way off-hand to improve that. However, if you’ve got some software or something that can write to the Oracle database AND that can also keep time in small increments, you could store timestamps as a NUMBER – or even a VARCHAR2 – datatype.
Perhaps your operating system can write to the database? Operating systems keep times in <1 second increments, IIRC. Athena, was that your workaround?
http://java.sun.com/products/jdk/1.1/docs/guide/jdbc/getstart/mapping.doc.html
Ticker and Athena, go about halfway down that page, and read about how you can use a Javascript program to get your precision down to one nanosecond, using datatype TIMESTAMP.
It seems on first glance that this will only work with Oracle version 9i (latest) – not with version 8.1.7 or earlier.
Hi,
I’m a grounchy oracle programmer who has been working with Oracle for 14 years. I’m grouchy since I typed in a long description, and hit the ESC key, as I do in vi, and my whole post disappeared. Anyway, here it is again: Oracle stores dates as a seven byte data structure with the bytes being century,year,month,day,hour,minute,second. It store them that way since most applications report dates using some combination of those instead of the number of seconds since 1/1/1970. You want to store a timestamp down to some fraction of a second. If you’re planning on sorting you’re data in Oracle based on this timestamp, it would be a good idea to store it as a numeric offset from so fixed epoch, such as 1/1/1970 or 1/1/1900. The column can be created thusly:
create table bla_bla(
.
.
.
creation_ts number
.
.
.
);
If you want to impose some precision of that number, do “number(*,3)” which means three digits to the right of the decimal. To insert data, something like the following will do
insert into table bla_bla(…,creation_ts,…)
values(…,(to_date(:our_time,‘YYYYMMDDHH24MISS’)
-to_date(:the_epoch,'YYYYMMDDHH24MISS))*86400
+:our_fraction_of_a_second,…);
Subtracting dates in oracle results in the number of days between the two dates. 86400 is the number of seconds in a day. To simplify this a bit, you can create a package on the database, and call it to insert/update the data. You may look at $ORACLE_HOME/rdbms/admin/dbmsutil.sql for the get_time function. That one gives you the number of 100ths of a second since some arbitrary epoch. Your package could have some initialization pl/sql that translates that to your epoch. Your programmer can, of course, compute the offset from you epoch, and just insert that into you number field.
Hope this helps, if you give me more description of what you use this data for, I can give more info.
:o = : o put together
Forget the bit about sorting, minor brain fart there: thinking about strings.
Thanks for the responses people, much appreciated here.
I passed them on to Gordon, my co-worker, and he will probably go the roll-your-own route as NotMrKnowItAll suggested.
This all sucks though, 'cos I remeber using TIMESTAMP with milliseconds in DB/2 ten years ago and I was sure a big grown-up DBMS like Oracle would have similar capabilities.