This discussion is archived
5 Replies Latest reply: Dec 23, 2009 3:40 AM by Karthick_Arp RSS

Calculate date difference in Milliseconds

NikhilJuneja Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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
    NikhilJuneja Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    695451 Expert
    Currently Being Moderated
    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_Arp Guru
    Currently Being Moderated
    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

Legend

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