Q:

Substraction with decimal in ORACLE SQL

I need to substract 2 timestamps in the given format: 
16/01/17 07:01:06,165000000 
16/01/17 07:01:06,244000000

I want to express the result with 2 decimal values but somewhere in the CAST process I am loosing precision. My atempt by now goes this way: 
select 
  id,
  trunc((CAST(MAX(T.TIMESTAMP) AS DATE) - CAST(MIN(T.TIMESTAMP) AS DATE))*24*60*60,2) as result 
from table T
group by id;

But I get id_1 '0' as a result for the two timestamps above even after I set the truncate decimals at 2.
Is there a way that I can obtain the 0.XX aa a result of the substraction?

A:

It's because you are casting the timestamp to date.
Use to_timestamp to convert your string into timestamp.
Try this:
with your_table(tstamp) as (
  select '16/01/17 07:01:06,165000000' from dual union all
  select '16/01/17 07:01:06,244000000' from dual
),
your_table_casted as (
    select to_timestamp(tstamp,'dd/mm/yy hh24:mi:ss,ff') tstamp from your_table
)
select trunc(sysdate + (max(tstamp) - min(tstamp)) * 86400 - sysdate, 2) diff 
from your_table_casted;

The difference between two timestamps is INTERVAL DAY TO SECOND.
To convert it into seconds, use the above trick.

