Tom Kyte suggests to use EXTRACT to get the difference:
extract( day from (x-y) )*24*60*60+
extract( hour from (x-y) )*60*60+
...
This seems to be harder to read and slower than this, for example:
( CAST( x AS DATE ) - CAST( y AS DATE ) ) * 86400
So, what is the way to get the difference between two Timestamps in seconds? Thanks!
for fast and easy use:
extract( day from(t2 - t1)*24*60*60)
Example:
with dates as (
select
to_timestamp('2019-06-18 22:50:00', 'yyyy-mm-dd hh24:mi:ss') t1
, to_timestamp('2019-06-19 00:00:38', 'yyyy-mm-dd hh24:mi:ss') t2
from dual
)
select
extract( day from(t2 - t1)*24*60*60) diff_in_seconds
from dates
;
Output:
DIFF_IN_SECONDS
---------------
638