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

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

This question is related to sql oracle datetime

The answer is


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...


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

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
    )
);

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;

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!


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 => milliseconds; 1 => seconds; 2 => minutes; 3 => hours; 4 => 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

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))

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;

Select date1 - (date2 - 1) * 24 * 60 *60 * 1000 from Table;

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

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to oracle

concat yesterdays date with a specific time ORA-28001: The password has expired how to modify the size of a column How to create a blank/empty column with SELECT query in oracle? Find the number of employees in each department - SQL Oracle Query to display all tablespaces in a database and datafiles When or Why to use a "SET DEFINE OFF" in Oracle Database How to insert date values into table error: ORA-65096: invalid common user or role name in oracle In Oracle SQL: How do you insert the current date + time into a table?

Examples related to datetime

Comparing two joda DateTime instances How to format DateTime in Flutter , How to get current time in flutter? How do I convert 2018-04-10T04:00:00.000Z string to DateTime? How to get current local date and time in Kotlin Converting unix time into date-time via excel Convert python datetime to timestamp in milliseconds SQL Server date format yyyymmdd Laravel Carbon subtract days from current date Check if date is a valid one Why is ZoneOffset.UTC != ZoneId.of("UTC")?