The trouble with using the DATE datatype is doing arithmetic on the column in order to figure out the number of years, weeks, days, hours, and seconds between two dates. When doing subtraction between dates, we get a number that represents the number of days. We should then multiply that number by the number of seconds in a day (86400) before we continue with calculations to determine the interval with which we are concerned.
See the example below:
select to_char(source_created_dt,'dd.mm.yyyy:hh24:mi:ss') as creation_date,
to_char(source_changed_dt,'dd.mm.yyyy:hh24:mi:ss') as modification_date,
trunc(86400*(source_changed_dt-source_created_dt))-60*(trunc((86400*(source_changed_dt-source_created_dt))/60)) as secs,
trunc((86400*(source_changed_dt-source_created_dt))/60)-60*(trunc(((86400*(source_changed_dt-source_created_dt))/60)/60)) as mins,
trunc(((86400*(source_changed_dt-source_created_dt))/60)/60)-24*(trunc((((86400*(source_changed_dt-source_created_dt))/60)/60)/24)) as hrs,
trunc((((86400*(source_changed_dt-source_created_dt))/60)/60)/24) as days,
(extract(day from ((source_changed_dt-source_created_dt) day to second))*24) +
extract(hour from ((source_changed_dt-source_created_dt) day to second))':'
substr('0'extract(minute from ((source_changed_dt-source_created_dt) day to second)), -2) ':'
substr('0'extract(second from ((source_changed_dt - source_created_dt) day to second)), -2) as str_hms
from tbl_date_test;
