URGETNT !! Oracle date format millisecond

[vijay_3103]

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.

[Tarry]

Yes there is.

[gopi]

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

[Tarry]

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!

[gopi]

sorry Tarry.....dang I'm moody today!!! I hope it passes soon!

[vijay_3103]

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

[Tarry]

Vijay,
From 815 and up try this...
1. write a java class.
2. write a fxn in oracle
here's the 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

It's also possible to do in 8.0.

Cheers!!!

Tarry

[jmodic]

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.

[Tarry]

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.

Jurij,
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?

[pando]

well I think jmodic means you store timestamp with milliseconds (by calling java function) as varchar2 then you simply do simple queries