Calculate difference between 2 date / times in Oracle SQL

Question

Hi,

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

Answer 2

You could use to_timestamp function to convert the dates to timestamps and perform a substract operation.

Something like:

select to_timestamp ('13.10.1990 00:00:00','DD.MM.YYYY HH24:MI:SS') - TO_TIMESTAMP ('01.01.1990:00:10:00','DD.MM.YYYY:HH24:MI:SS')from dual

Answer 3

$sql="select bsp_bp,user_name,status,to_char(ins_date,'dd/mm/yyyy hh12:mi:ss AM'),to_char(pickup_date,'dd/mm/yyyy hh12:mi:ss AM'),trunc((pickup_date-ins_date)2460*60,2),message,status_message from valid_bsp_req where id >= '$id'";

with regards sujan