3 Replies Latest reply: Jul 3, 2012 4:18 PM by Gennady Sigalaev RSS

    How to insert default time with milliseconds

    936666
      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
          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-Oracle
            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
              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