i use oracle date format to_date(YYYY-MM-DD HH24:MI:SS) to insert data values into oracle.
IS there way to store up to millisecond precision in oracle8i.
Thanks
Vijay.
Printable View
i use oracle date format to_date(YYYY-MM-DD HH24:MI:SS) to insert data values into oracle.
IS there way to store up to millisecond precision in oracle8i.
Thanks
Vijay.
Yes there is.
What does arrogance gain us?
----
vijay,
Lookup timestamp (although I'm not 100% certain this did not start in 9i).
define the column as timestamp;
insert into temp_table (COL1) values (timestamp '1999-12-01 11:30:55.3');
SQL> select col1 from temp_table;
COL1
---------------------------------------------
01-DEC-99 11.30.55.300000 AM
SQL> desc temp_table
Name Type Nullable Default Comments
---- ---------------- -------- ------- --------
COL1 TIMESTAMP(6)(11) Y
INSERT into temp_table (col1) SELECT substr(CURRENT_TIMESTAMP,1, 26) FROM DUAL
Gopi,
timestamp datatype was introduced in 9i.
and he asked about 8i, i said it's possible.
He didn't ask how.
It's got nothing to do with arrogance!
sorry Tarry.....dang I'm moody today!!! I hope it passes soon!
Hi Tarry,
How do we do that in 8i.
I am using java-oracle.i flipped over certain pages in the net which said u can ise java.sql.timestamp to do that.
i am not sure how to implement that..
If thats not the way u thoght abt ,just pour in your ideas too..
Thanks Again
Vijay
Vijay,
From 815 and up try this...
1. write a java class.
2. write a fxn in oracle
here's the code...
It's also possible to do in 8.0.Code:SQL*Plus: Release 8.1.6.0.0 - Production on Mon Aug 26 16:20:09 2002
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
appdev@NICK.WAGENBORG.COM>create or replace JAVA SOURCE
2 NAMED "MyTimeStamp"
3 AS
4 import java.lang.String;
5 import java.sql.Timestamp;
6
7
8 public class MyTimeStamp
9 {
10 public static String getTimestamp()
11 {
12 return (new Timestamp(System.currentTimeMillis())).toString();
13 }
14 };
15 /
Java created.
appdev@NICK.WAGENBORG.COM>create or replace function my_timestamp return varchar2
2 AS LANGUAGE JAVA
3 NAME 'MyTimeStamp.getTimestamp() return java.lang.String';
4 /
Function created.
appdev@NICK.WAGENBORG.COM>col my_timestamp format a25
appdev@NICK.WAGENBORG.COM>col normal format a25
appdev@NICK.WAGENBORG.COM>select my_timestamp, to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') normal
2 from dual;
MY_TIMESTAMP NORMAL
------------------------- -------------------------
2002-08-26 16:42:37.226 2002-08-26 16:42:37
Cheers!!!
Tarry
Hm, but the question was how to store miliseconds in Oracle 8i, not how to retrieve it from a computer system!
The answer is: you can't store it in a DATE datatype column, you can only store it within VHARCHAR2 (or similar) datatype. And you don't need java for that, you can simply store it as a literal string.
Jurij,Quote:
Originally posted by jmodic
you can't store it in a DATE datatype column, you can only store it within VHARCHAR2 (or similar) datatype. And you don't need java for that, you can simply store it as a literal string.
Well i'm curious to see how you can store it and then(i presume also want to retrieve it) without a callout to a java stored procedure or other?
Maybe a sample please?
well I think jmodic means you store timestamp with milliseconds (by calling java function) as varchar2 then you simply do simple queries
Code:SQL> create table my_table(my_comment VARCHAR2(100),
2 my_date_with_milliseconds VARCHAR2(25));
Table created.
SQL> insert into my_table values
2 ('The time in millisecs I''ve got from somewhere',
3 '2002-08-26 16:42:37.226');
1 row created.
SQL> select * from my_table;
MY_COMMENT
--------------------------------------------------------
MY_DATE_WITH_MILLISECONDS
-------------------------
The time in millisecs I've got from somewhere
2002-08-26 16:42:37.226
Nice