Calculating difference between two timestamps in Oracle in milliseconds

Question

How do I calculate the time difference in milliseconds between two timestamps in Oracle

User · Answer

I  if you need to calculate the elapsed time in seconds between two timestamp columns try this  SELECT      extract   day from  end timestamp - start timestamp    86400        extract   hour from  end timestamp - start timestamp    3600        extract   minute from  end timestamp - start timestamp    60        extract   second from  end timestamp - start timestamp     FROM table name  II  if you want to just show the time difference in character format try this  SELECT to char  end timestamp - start timestamp  FROM table name

User · Answer

Select date1 -  date2 - 1    24   60  60   1000 from Table

User · Answer

The timestamp casted correctly between formats else there is a chance the fields would be misinterpreted   Here is a working sample that is correct when two different dates  Date2  Date1  are considered from table TableXYZ    SELECT ROUND  totalSeconds    24   60   60   1  TotalTimeSpendIn DAYS         ROUND  totalSeconds    60   60   0  TotalTimeSpendIn HOURS         ROUND  totalSeconds   60  TotalTimeSpendIn MINUTES         ROUND  totalSeconds  TotalTimeSpendIn SECONDS   FROM  SELECT ROUND                       EXTRACT  DAY FROM timeDiff    24   60   60                     EXTRACT  HOUR FROM timeDiff    60   60                     EXTRACT  MINUTE FROM timeDiff    60                     EXTRACT  SECOND FROM timeDiff                     totalSeconds            FROM  SELECT TO TIMESTAMP                               TO CHAR  Date2                                        yyyy-mm-dd HH24 mi ss                             -  yyyy-mm-dd HH24 mi ss                           TO TIMESTAMP                             TO CHAR  Date1                                      yyyy-mm-dd HH24 mi ss                               yyyy-mm-dd HH24 mi ss                             timeDiff                   FROM TableXYZ

User · Answer

I know that many people finding this solution simple and clear  create table diff timestamp   f1 timestamp   f2 timestamp    insert into diff timestamp values systimestamp-1  systimestamp 2   commit   select cast f2 as date  - cast f1 as date  from diff timestamp   bingo

User · Answer

Better to use procedure like that   CREATE OR REPLACE FUNCTION timestamp diff   start time in TIMESTAMP   end time in TIMESTAMP   RETURN NUMBER AS l days NUMBER  l hours NUMBER  l minutes NUMBER  l seconds NUMBER  l milliseconds NUMBER  BEGIN SELECT extract DAY FROM end time in-start time in    extract HOUR FROM end time in-start time in    extract MINUTE FROM end time in-start time in    extract SECOND FROM end time in-start time in  INTO l days  l hours  l minutes  l seconds FROM dual   l milliseconds    l seconds 1000   l minutes 60 1000   l hours 60 60 1000   l days 24 60 60 1000  RETURN l milliseconds   END    You can check it by calling   SELECT timestamp diff  TO TIMESTAMP  12 04 2017 12 00 00 00    DD MM YYYY HH24 MI SS FF                           TO TIMESTAMP  12 04 2017 12 00 01 111    DD MM YYYY HH24 MI SS FF                 as milliseconds     FROM DUAL

User · Answer

I ve posted here some methods to convert interval to nanoseconds and nanoseconds to interval  These methods have a nanosecond precision  You just need to adjust it to get milliseconds instead of nanoseconds  A shorter method to convert interval to nanoseconds  SELECT  EXTRACT DAY FROM       INTERVAL   18500 09 33 47 263027  DAY 5  TO SECOND --Replace line with desired interval --Maximum value  INTERVAL   694444 10 39 59 999999999  DAY 6  TO SECOND 9  or up to 3871 year     24   60    60   EXTRACT SECOND FROM       INTERVAL   18500 09 33 47 263027  DAY 5  TO SECOND --Replace line with desired interval       100 AS MILLIS FROM DUAL   MILLIS 1598434427263 027

User · Answer

Easier solution   SELECT numtodsinterval date1-date2  day   time difference from dates    For timestamps   SELECT  extract DAY FROM time2-time1  24 60 60     extract HOUR FROM time2-time1  60 60    extract MINUTE FROM time2-time1  60   extract SECOND FROM time2-time1  into diff FROM dual   RETURN diff

User · Answer

Here s a stored proc to do it   CREATE OR REPLACE function timestamp diff a timestamp  b timestamp  return number is  begin   return extract  day    from  a-b   24 60 60            extract  hour   from  a-b   60 60           extract  minute from  a-b   60           extract  second from  a-b    end      Up Vote if you also wanted to beat the crap out of the Oracle developer who negated to his job      BECAUSE comparing timestamps for the first time should take everyone an hour or so

User · Answer

I know that this has been exhaustively answered  but I wanted to share my FUNCTION with everyone  It gives you the option to choose if you want your answer to be in days  hours  minutes  seconds  or milliseconds  You can modify it to fit your needs   CREATE OR REPLACE FUNCTION Return Elapsed Time  start  IN TIMESTAMP  end  IN TIMESTAMP DEFAULT SYSTIMESTAMP  syntax  IN NUMBER DEFAULT NULL  RETURN VARCHAR2 IS     FUNCTION Core  start  IN TIMESTAMP  end  IN TIMESTAMP DEFAULT SYSTIMESTAMP  syntax  IN NUMBER DEFAULT NULL  RETURN VARCHAR2 IS         day  VARCHAR2 7      This means this FUNCTION only supports up to 99 days            hour  VARCHAR2 9      This means this FUNCTION only supports up to 999 hours  which is over 41 days            minute  VARCHAR2 12      This means this FUNCTION only supports up to 9999 minutes  which is over 17 days            second  VARCHAR2 18      This means this FUNCTION only supports up to 999999 seconds  which is over 11 days            msecond  VARCHAR2 22      This means this FUNCTION only supports up to 999999999 milliseconds  which is over 11 days            d1  NUMBER          h1  NUMBER          m1  NUMBER          s1  NUMBER          ms  NUMBER             If you choose 1  you only get seconds  If you choose 2  you get minutes and seconds etc             precision  NUMBER     0   gt  milliseconds  1   gt  seconds  2   gt  minutes  3   gt  hours  4   gt  days            format  VARCHAR2 2                   return  VARCHAR2 50       BEGIN         IF  syntax  IS NULL  THEN             precision     0          ELSE             IF  syntax    0  THEN                 precision     0              ELSIF  syntax    1  THEN                 precision     1              ELSIF  syntax    2  THEN                 precision     2              ELSIF  syntax    3  THEN                 precision     3              ELSIF  syntax    4  THEN                 precision     4              ELSE                  precision     0              END IF          END IF          SELECT EXTRACT DAY FROM  end  - start    INTO d1  FROM DUAL          SELECT EXTRACT HOUR FROM  end  - start    INTO h1  FROM DUAL          SELECT EXTRACT MINUTE FROM  end  - start    INTO m1  FROM DUAL          SELECT EXTRACT SECOND FROM  end  - start    INTO s1  FROM DUAL          IF  precision    4  THEN             IF  d1    1  THEN                 day       day               ELSE                 day       days               END IF              IF  h1    1  THEN                 hour       hour               ELSE                 hour       hours               END IF              IF  m1    1  THEN                 minute       minute               ELSE                 minute       minutes               END IF              IF  s1    1  THEN                 second       second               ELSE                 second       seconds               END IF              return     d1     day     format     h1     hour     format     m1     minute     format     s1     second               RETURN return           ELSIF  precision    3  THEN             h1      d1    24    h1               IF  h1    1  THEN                 hour       hour               ELSE                 hour       hours               END IF              IF  m1    1  THEN                 minute       minute               ELSE                 minute       minutes               END IF              IF  s1    1  THEN                 second       second               ELSE                 second       seconds               END IF              return     h1     hour     format     m1     minute     format     s1     second               RETURN return           ELSIF  precision    2  THEN             m1        d1    24    h1     60    m1               IF  m1    1  THEN                 minute       minute               ELSE                 minute       minutes               END IF              IF  s1    1  THEN                 second       second               ELSE                 second       seconds               END IF              return     m1     minute     format     s1     second               RETURN return           ELSIF  precision    1  THEN             s1          d1    24    h1     60    m1     60    s1               IF  s1    1  THEN                 second       second               ELSE                 second       seconds               END IF              return     s1     second               RETURN return           ELSE             ms           d1    24    h1     60    m1     60    s1     1000              IF  ms    1  THEN                 msecond       millisecond               ELSE                 msecond       milliseconds               END IF              return     ms     msecond               RETURN return           END IF      END Core  BEGIN     RETURN Core start   end   syntax     END Return Elapsed Time    For example  if I called this function right now  12 10 2018 11 17 00 00  using Return Elapsed Time TO TIMESTAMP  12 04 2017 12 00 00 00    DD MM YYYY HH24 MI SS FF   SYSTIMESTAMP   it should return something like   47344620000 milliseconds

User · Answer

When you subtract two variables of type TIMESTAMP  you get an INTERVAL DAY TO SECOND which includes a number of milliseconds and or microseconds depending on the platform   If the database is running on Windows  systimestamp will generally have milliseconds   If the database is running on Unix  systimestamp will generally have microseconds     1  select systimestamp - to timestamp   2012-07-23    yyyy-mm-dd      2    from dual SQL gt     SYSTIMESTAMP-TO TIMESTAMP  2012-07-23   YYYY-MM-DD   ---------------------------------------------------------------------------  000000000 14 51 04 339000000   You can use the EXTRACT function to extract the individual elements of an INTERVAL DAY TO SECOND   SQL gt  ed Wrote file afiedt buf    1  select extract  day from diff   days    2         extract  hour from diff   hours    3         extract  minute from diff   minutes    4         extract  second from diff   seconds   5    from  select systimestamp - to timestamp   2012-07-23    yyyy-mm-dd    diff   6            from dual  SQL gt           DAYS      HOURS    MINUTES    SECONDS ---------- ---------- ---------- ----------          0         14         55     37 936   You can then convert each of those components into milliseconds and add them up  SQL gt  ed Wrote file afiedt buf    1  select extract  day from diff   24 60 60 1000     2         extract  hour from diff   60 60 1000     3         extract  minute from diff   60 1000     4         round extract  second from diff   1000  total milliseconds   5    from  select systimestamp - to timestamp   2012-07-23    yyyy-mm-dd    diff   6            from dual  SQL gt     TOTAL MILLISECONDS ------------------           53831842   Normally  however  it is more useful to have either the INTERVAL DAY TO SECOND representation or to have separate columns for hours  minutes  seconds  etc  rather than computing the total number of milliseconds between two TIMESTAMP values

User · Answer

Above one has some syntax error  Please use following on oracle    SELECT ROUND  totalSeconds    24   60   60   1  TotalTimeSpendIn DAYS    ROUND  totalSeconds         60   60   0  TotalTimeSpendIn HOURS    ROUND  totalSeconds        60  TotalTimeSpendIn MINUTES    ROUND  totalSeconds  TotalTimeSpendIn SECONDS FROM    SELECT ROUND   EXTRACT  DAY FROM timeDiff    24   60   60   EXTRACT  HOUR FROM timeDiff    60   60   EXTRACT  MINUTE FROM timeDiff    60   EXTRACT  SECOND FROM timeDiff   totalSeconds   FROM      SELECT TO TIMESTAMP TO CHAR  date2    yyyy-mm-dd HH24 mi ss     yyyy-mm-dd HH24 mi ss   - TO TIMESTAMP TO CHAR date1   yyyy-mm-dd HH24 mi ss    yyyy-mm-dd HH24 mi ss   timeDiff     FROM TABLENAME

[sql] Calculating difference between two timestamps in Oracle in milliseconds

Examples related to sql

Examples related to oracle

Examples related to datetime