This discussion is archived
3 Replies Latest reply: Jul 3, 2012 2:18 PM by Gennady Sigalaev RSS

How to insert default time with milliseconds

936666 Newbie
Currently Being Moderated
How to insert default time with milliseconds

I tried with two ways but not able to insert millisecond

CASE:1

Command> create table Test (i int,h timestamp default TO_CHAR(SYSDATE, 'DD-MON-
YYYY HH:MI:SS:SSSSS'));

1001: Syntax error in SQL statement before or at: "(", character position: 53
... table Test (i int,h timestamp default TO_CHAR(SYSDATE, 'DD-MON-YY...
The command failed.

-----------------------------------------------------------------------------------------------------------------------------------------------------------
CASE:2

Command> create table Test (i int,h timestamp default Sysdate );

Command> Insert into test values (1,TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MI:SS:SSSSS'));
2813: Error converting from character string '2012-07-03 10:58:56:395366' to Oracle timestamp
The command failed.

I need to insert the data with default time with milliseconds

Thanks!
  • 1. Re: How to insert default time with milliseconds
    936666 Newbie
    Currently Being Moderated
    Hi,
    I am able to do
    CASE:2

    Command> create table Test (i int,h timestamp default Sysdate );

    Command> Insert into test values (1,TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MI:SS.SSSSS'));

    it should be ss.ssss not ss:ssss

    But i couldn't find way for Case:1

    And i need to find the Min time -Max time

    Select Max(h)-Min(h) from Test;
    Getting error

    Need to get the difference of the h in millisecond

    Thanks!!!
  • 2. Re: How to insert default time with milliseconds
    rajeshp Explorer
    Currently Being Moderated
    Fro case 1 , can you try something below

    Command> create table test (i int,h timestamp default TT_sysdate);
    Command> insert into test(i) values(1);
    1 row inserted.
    Command> select * from test;
    < 1, 2012-07-04 00:38:04.250000 >
    1 row found.

    You can use interval function to find the difference between two dates like below

    select extract(second from max(h)-min(h)) from test;
  • 3. Re: How to insert default time with milliseconds
    Gennady Sigalaev Journeyer
    Currently Being Moderated
    Dear 933663,
    Select Max(h)-Min(h) from Test;
    Getting error
    Need to get the difference of the h in millisecond
    I think you are looking for something like the following:
    Command> create table t2 (id number, t_date timestamp);
    Command> insert into t2 values (1, tt_sysdate);
    1 row inserted.
    Command> insert into t2 values (2, tt_sysdate);
    1 row inserted.
    Command> select * from t2;
    < 1, 2012-07-03 16:45:24.906242 >
    < 2, 2012-07-03 16:45:27.806508 >
    2 rows found.
    Command> SELECT trunc( 1000*( EXTRACT(DAY    FROM max(t_date)-min(t_date))*24*3600 +
                                  EXTRACT(HOUR   FROM max(t_date)-min(t_date))*3600 +
                                  EXTRACT(MINUTE FROM max(t_date)-min(t_date))*60+
                                  EXTRACT(SECOND FROM max(t_date)-min(t_date))
                                 )
                         )
             FROM t2;
    < 2900 >
    1 row found.
    Command>
    regards,
    Gennady

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points