URGETNT !! Oracle date format millisecond
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: URGETNT !! Oracle date format millisecond

  1. #1
    Join Date
    Jul 2002
    Posts
    11
    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.

  2. #2
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,591
    Yes there is.
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  3. #3
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510
    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

  4. #4
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,591
    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!
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  5. #5
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510
    sorry Tarry.....dang I'm moody today!!! I hope it passes soon!

  6. #6
    Join Date
    Jul 2002
    Posts
    11
    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

  7. #7
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,591
    Vijay,
    From 815 and up try this...
    1. write a java class.
    2. write a fxn in oracle
    here's the code...
    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
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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 Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,591
    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?
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    well I think jmodic means you store timestamp with milliseconds (by calling java function) as varchar2 then you simply do simple queries

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width