Subtracting timestamp in oracle returning weird data

oracle, subtraction, timestamp

Solution

I guess your columns are defined as `timestamp` rather than `date`.

The result of subtracting timestamps is an `interval` whereas the result of subtracting `date` columns is a number representing the number of days between the two dates.

This is documented in the manual: http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements001.htm#i48042

So when you cast your timestamp columns to date, you should get what you expect:

with dates as (
   select timestamp '2012-04-27 09:00:00' as col1,
          timestamp '2012-04-26 17:35:00' as col2
   from dual
)
select col1 - col2 as ts_difference,
       cast(col1 as date) - cast(col2 as date) as dt_difference
from dates;

Edit:

If you want to convert the interval so e.g. the number of seconds (as a number), you can do something like this:

with dates as (
   select timestamp '2012-04-27 09:00:00.1234' as col1,
          timestamp '2012-04-26 17:35:00.5432' as col2
   from dual
)
select col1 - col2 as ts_difference,
       extract(hour from (col1 - col2)) * 3600 +  
       extract(minute from (col1 - col2)) * 60 + 
       (extract(second from (col1 - col2)) * 1000) / 1000 as seconds
from dates;

The result of the above is `55499.5802`

Problem

I'm trying to subtract two dates and expecting some floating values return. But what I got in return is as below: ``` +000000000 00:00:07.225000 ``` Multiplying the value by 86400 (I want to get the difference in second) is getting something even more strange value being returned: ``` +000000007 05:24:00.000000000 ``` any idea? I'm suspecting is has something to do with type casting.

Original source