Calculate difference between 2 date / times in Oracle SQL

Question

I have a table as follows:

Filename - varchar
Creation Date - Date format dd/mm/yyyy hh24:mi:ss
Oldest cdr date - Date format dd/mm/yyyy hh24:mi:ss

How can I calcuate the difference in hours minutes and seconds (and possibly days) between the two dates in Oracle SQL?

Thanks

Answer 1

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.

Answer 2

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]

Answer 3

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