Why do I get an incompatible value type for my column?

Question

I am trying to calculate the difference between two dates in an oracle database using a JDBC connection. I followed the advice from this question using a query like this:

SELECT CREATE_DATE - CLOSED
    FROM TRANSACTIONS;

and I get the following error:

Incompatible value type specified for 
column:CREATE_DATE-CLOSED. Column Type = 11 and Value Type = 
8.[10176] Error Code: 10176

What should I change so I can successfully calculate the difference between the dates?

note: CREATE_DATE and CLOSED both have TIMESTAMP type

Answer 1

The answer you found is related to date datatypes, but you are dealing with timestamps. While substracting two Oracle dates returns a number, substracting timestamps produces an interval datatype. This is probably not what you want, and, apparently, your driver does not properly handle this datatype.

For this use case one solution is to cast the timestamps to dates before substracting them:

select cast(create_date as date) - cast(closed as date) from transactions;

Answer 2

As it was mentioned, it seems that JDBC cannot work with the INTERVAL datatype. What about casting it with the EXTRACT function to the expected output as number? If you want number of seconds between those two timestamps, it would be:

SELECT EXTRACT(SECOND FROM (CREATE_DATE - CLOSED)) FROM TRANSACTIONS;

Here are list of options which might be used instead of SECOND: https://docs.oracle.com/database/121/SQLRF/functions067.htm#SQLRF00639

Answer 3

When we subtract one date from another Oracle gives us the difference as a number: it's straightforward arithmetic. But when we subtract one timestamp from another - which is what you're doing - the result is an INTERVAL. Older versions of JDBC don't like the INTERVAL datatype (docs) .

Here are a couple of workarounds, depending on what you want to do with the result. The first is to calculate the number of seconds from the interval result. extract second from ... only gives us the numbers of seconds in the interval. This will be fine providing none of your intervals are more than fifty-nine seconds long. Longer intervals require us to extract minute, hour and even days. So that solution would be:

select t.*
       ,   extract (day    from (t.closed - t.create_date)) * 84600 
         + extract (hour   from (t.closed - t.create_date)) * 3600
         + extract (minute from (t.closed - t.create_date)) * 60 
         + extract (second from (t.closed - t.create_date)) as no_of_secs
from transactions t 

A second solution is to follow the advice in the JDBC mapping guide and turn the interval into a string:

select t.*
       , cast ((t.closed - t.create_date) as varchar2(128 char)) as intrvl_str
from transactions t  

The format of a string interval is verbose:INTERVAL'+000000001 04:40:59.710000'DAY(9)TO SECOND. This may not be useful in the Java side of the application. But with regex we can turn it into a string which can be converted into a Java 8 Duration object (docs) : PnDTnHnMn.nS.

select t.id
       , regexp_replace(cast ((t.closed - t.create_date) as varchar2(128 char))
                        , 'INTERVAL''\+([0-9]+) ([0-9]{2}):([0-9]{2}):([0-9]{2})\.([0-9]+)''DAY\(9\)TO SECOND'
                        , 'P\1DT\2H\3M\4.\5S') 
        as duration
from transactions t

There is a demo on db<>fiddle