Calculate difference between 2 date / times in Ora

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

select days||' '|| time from (
SELECT to_number( to_char(to_date('1','J') +
    (CLOSED_DATE - CREATED_DATE), 'J') - 1)  days,
   to_char(to_date('00:00:00','HH24:MI:SS') +
      (CLOSED_DATE - CREATED_DATE), 'HH24:MI:SS') time
 FROM  request  where REQUEST_ID=158761088 );

Answer 2

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.

Answer 3

Calculate age from HIREDATE to system date of your computer

SELECT HIREDATE||'        '||SYSDATE||'       ' ||
TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12) ||' YEARS '||
TRUNC((MONTHS_BETWEEN(SYSDATE,HIREDATE))-(TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12)*12))||
'MONTHS' AS "AGE  "  FROM EMP;

Answer 4

If you select two dates from 'your_table' and want too see the result as a single column output (eg. 'days - hh:mm:ss') you could use something like this. First you could calculate the interval between these two dates and after that export all the data you need from that interval:

         select     extract (day from numtodsinterval (second_date
                                                   - add_months (created_date,
                                                                 floor (months_between (second_date,created_date))),
                                                   'day'))
             || ' days - '
             || extract (hour from numtodsinterval (second_date
                                                    - add_months (created_date,
                                                                  floor (months_between (second_date,created_date))),
                                                    'day'))
             || ':'
             || extract (minute from numtodsinterval (second_date
                                                      - add_months (created_date,
                                                                    floor (months_between (second_date, created_date))),
                                                      'day'))
             || ':'
             || extract (second from numtodsinterval (second_date
                                                      - add_months (created_date,
                                                                    floor (months_between (second_date, created_date))),
                                                      'day'))
     from    your_table

And that should give you result like this: 0 days - 1:14:55

Answer 5

select 
    extract( day from diff ) Days, 
    extract( hour from diff ) Hours, 
    extract( minute from diff ) Minutes 
from (
        select (CAST(creationdate as timestamp) - CAST(oldcreationdate as timestamp)) diff   
        from [TableName] 
     );

This will give you three columns as Days, Hours and Minutes.

Answer 6

select (floor(((DATE2-DATE1)*24*60*60)/3600)|| ' : ' ||floor((((DATE2-DATE1)*24*60*60) -floor(((DATE2-DATE1)*24*60*60)/3600)*3600)/60)|| '  ' ) as time_difference from TABLE1