5 Replies Latest reply: Dec 23, 2009 5:40 AM by _Karthick_ RSS

    Calculate date difference in Milliseconds

    Nikhil Juneja
      Hi All,

      Version :- Oracle Database 10g Enterprise Edition Release 10.2.0.3.0

      Requirement :- To calculate difference between current systimestamp and 1st Jan 1970 with precision upto milliseconds ( we require this to store in some table )

      The query which I have given below it gives value upto precision 1 sec

      select (sysdate - to_date('01-JAN-1970 00:00:00','DD-MON-YYYY HH24:MI;SS')) * (24 * 60 * 60 * 1000) from dual;

      Output
      1261545437000

      After 1 sec
      1261545439000 and so on

      can i get difference to the precision in millisecond
        • 1. Re: Calculate date difference in Milliseconds
          730428
          select (sysdate - to_date('01-JAN-1970','DD-MON-YYYY')) * (24 * 60 * 60 * 1000)+to_number(to_char(systimestamp,'FF3')) from dual;
          Max

          Edited by: Massimo Ruocchio on Dec 23, 2009 12:19 PM
          Corrected
          • 2. Re: Calculate date difference in Milliseconds
            Nikhil Juneja
            Thanks for the reply...
            It gives the below error

            SQL> select (systimestamp - to_date('01-JAN-1970 00:00:00','DD-MON-YYYY HH24:MI;SS')) * (24 * 60 * 60 * 1000) from dual;
            select (systimestamp - to_date('01-JAN-1970 00:00:00','DD-MON-YYYY HH24:MI;SS')) * (24 * 60 * 60 * 1000) from dual
            *
            ERROR at line 1:
            ORA-01866: the datetime class is invalid


            SQL> select to_number((systimestamp - to_date('01-JAN-1970 00:00:00','DD-MON-YYYY HH24:MI;SS')) * (24 * 60 * 60 * 1000)) from dual;
            select to_number((systimestamp - to_date('01-JAN-1970 00:00:00','DD-MON-YYYY HH24:MI;SS')) * (24 * 60 * 60 * 1000)) from dual
            *
            ERROR at line 1:
            ORA-01891: Datetime/Interval internal error
            • 3. Re: Calculate date difference in Milliseconds
              730428
              Please see my correction in my first post.
              SQL >set numw 20
              SQL >select (sysdate - to_date('01-GEN-1970','DD-MON-YYYY')) 
                2         * (24 * 60 * 60 * 1000)
                3         +to_number(to_char(systimestamp,'FF3')) 
                4    from dual;
              
              (SYSDATE-TO_DATE('01-GEN-1970','DD-MON-YYYY'))*(24*60*60*1000)+TO_NUMBER(TO_CHAR
              --------------------------------------------------------------------------------
                                                                                 1261571507307
              
              SQL >select (sysdate - to_date('01-GEN-1970','DD-MON-YYYY')) 
                2         * (24 * 60 * 60 * 1000)
                3         +to_number(to_char(systimestamp,'FF3')) 
                4    from dual;
              
              (SYSDATE-TO_DATE('01-GEN-1970','DD-MON-YYYY'))*(24*60*60*1000)+TO_NUMBER(TO_CHAR
              --------------------------------------------------------------------------------
                                                                                 1261571508966
              
              SQL >select (sysdate - to_date('01-GEN-1970','DD-MON-YYYY')) 
                2         * (24 * 60 * 60 * 1000)
                3         +to_number(to_char(systimestamp,'FF3')) 
                4    from dual;
              
              (SYSDATE-TO_DATE('01-GEN-1970','DD-MON-YYYY'))*(24*60*60*1000)+TO_NUMBER(TO_CHAR
              --------------------------------------------------------------------------------
                                                                                 1261571510318
              Max

              Edited by: Massimo Ruocchio on Dec 23, 2009 12:32 PM
              • 4. Re: Calculate date difference in Milliseconds
                ravikumar.sv
                select (extract(day from systimestamp-to_date('19700101','YYYYMMDD'))*24*60*60+
                extract(hour from systimestamp-to_date('19700101','YYYYMMDD'))*60*60+
                extract(minute from systimestamp-to_date('19700101','YYYYMMDD'))*60+
                extract(second from systimestamp-to_date('19700101','YYYYMMDD')))*1000 timeinmilliseconds
                from dual
                you can round off the decimal part...if you just want only time in milliseconds..

                Ravi Kumar

                Edited by: ravikumar.sv on Dec 23, 2009 5:07 PM
                • 5. Re: Calculate date difference in Milliseconds
                  _Karthick_
                  Try this
                  with t
                  as
                  (
                  select systimestamp-to_timestamp('01-JAN-1970 00:00:00','DD-MON-YYYY HH24:MI:SS') dt from dual
                  )
                  select dt date_diff,
                         extract(day from dt) * 24 * 60 * 60 * 1000 +
                         extract(hour from dt) * 60 * 60 * 1000 +
                         extract(minute from dt) * 60 * 1000 +
                         extract(second from dt) * 1000 diff_in_ms
                    from t