If the dates are really timestamps, then this is easy - subtract them and the result is a day to second interval. These are already in hour:minute:second format!
with rws as (
select timestamp'2017-03-01 01:00:00' t1,
timestamp'2017-03-01 02:34:56' t2
from dual
)
select t2-t1 diff_interval
from rws;
DIFF_INTERVAL
+00 01:34:56.000000
Or you can get the components out manually with extract():
with rws as (
select timestamp'2017-03-01 01:00:00' t1,
timestamp'2017-03-01 02:34:56' t2
from dual
)
select extract(hour from (t2-t1)) hrs,
extract(minute from (t2-t1)) mins,
extract(second from (t2-t1)) secs,
t2-t1 diff_interval
from rws;
HRS MINS SECS DIFF_INTERVAL
1 34 56 +00 01:34:56.000000
If the values are dates, subtracting them yields a number. Extract only works on datetimes or intervals. So you'll get an error:
with rws as (
select date'2017-03-01' t1,
date'2017-03-01'+1/15.1 t2
from dual
)
select extract(hour from (t2-t1)) hrs,
extract(minute from (t2-t1)) mins,
extract(second from (t2-t1)) secs,
t2-t1 diff_interval
from rws;
SQL Error: ORA-30076: invalid extract field for extract source
But you can get around this by casting the dates to timestamps:
with rws as (
select date'2017-03-01' t1,
date'2017-03-01'+1/15.1 t2
from dual
), tstamps as (
select to_timestamp(to_char(t1, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss') t1,
to_timestamp(to_char(t2, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss') t2
from rws
)
select extract(hour from (t2-t1)) hrs,
extract(minute from (t2-t1)) mins,
extract(second from (t2-t1)) secs,
t2-t1 diff_interval
from tstamps;
HRS MINS SECS DIFF_INTERVAL
1 35 22 +00 01:35:22.000000