Calculate difference between 2 date / times in Oracle SQL

You can substract dates in Oracle. This will give you the difference in days. Multiply by 24 to get hours, and so on.

SQL> select oldest - creation from my_table;

If your date is stored as character data, you have to convert it to a date type first.

SQL> select 24 * (to_date('2009-07-07 22:00', 'YYYY-MM-DD hh24:mi')              - to_date('2009-07-07 19:30', 'YYYY-MM-DD hh24:mi')) diff_hours        from dual;DIFF_HOURS----------       2.5

Note:

This answer applies to dates represented by the Oracle data type DATE.Oracle also has a data type TIMESTAMP, which can also represent a date (with time). If you subtract TIMESTAMP values, you get an INTERVAL; to extract numeric values, use the EXTRACT function.

To get result in seconds:

select (END_DT - START_DT)*60*60*24 from MY_TABLE;

Check [https://community.oracle.com/thread/2145099?tstart=0][1]

declarestrTime1 varchar2(50) := '02/08/2013 01:09:42 PM';strTime2 varchar2(50) := '02/08/2013 11:09:00 PM';v_date1 date := to_date(strTime1,'DD/MM/YYYY HH:MI:SS PM');v_date2 date := to_date(strTime2,'DD/MM/YYYY HH:MI:SS PM');difrence_In_Hours number;difrence_In_minutes number;difrence_In_seconds number;begin    difrence_In_Hours   := (v_date2 - v_date1) * 24;    difrence_In_minutes := difrence_In_Hours * 60;    difrence_In_seconds := difrence_In_minutes * 60;    dbms_output.put_line(strTime1);            dbms_output.put_line(strTime2);    dbms_output.put_line('*******');    dbms_output.put_line('difrence_In_Hours  : ' || difrence_In_Hours);    dbms_output.put_line('difrence_In_minutes: ' || difrence_In_minutes);    dbms_output.put_line('difrence_In_seconds: ' || difrence_In_seconds);        end ;

Hope this helps.