How does one get the time difference between two date columns?

select floor(((date1-date2)*24*60*60)/3600)

|| ' HOURS ' ||

floor((((date1-date2)*24*60*60) -

floor(((date1-date2)*24*60*60)/3600)*3600)/60)

|| ' MINUTES ' ||

round((((date1-date2)*24*60*60) -

floor(((date1-date2)*24*60*60)/3600)*3600 -

(floor((((date1-date2)*24*60*60) -

floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60)))

|| ' SECS ' time_difference

from ...

If you don't want to go through the floor and ceiling math, try this method (contributed by Erik Wile):

select to_char(to_date('00:00:00','HH24:MI:SS') +

(date1 - date2), 'HH24:MI:SS') time_difference

from ...

Note that this query only uses the time portion of the date and ignores the date itself. It will thus never return a value bigger than 23:59:59.