ORA-00932: inconsistent datatypes: expected INTERVAL DAY TO SECOND got CHAR

Question

SELECT COALESCE (
      (to_timestamp( '2014-09-22 16:00:00','yyyy/mm/dd HH24:MI:SS')
        -   ('2014-09-22   09:00:00' ,'yyyy/mm/dd HH24:MI:SS'))  
        - (to_timestamp( '2014-09-22 16:00:00','yyyy/mm/dd HH24:MI:SS')  
        - to_timestamp('2014-09-22 09:00:00.' ,'yyyy/mm/dd HH24:MI:SS')) 
       , '00:00') 
FROM DUAL;

This is working in postgres but it is not working in oracle.

Answer 1

It looks like you are trying to do maths (+, -) with TIMESTAMP. TIMESTAMP doesn't like that. you should CAST the TIMESTAMP to DATE:

rather than bla - blu (where bla and blu are TIMESTAMP) do

CAST (bla as DATE) - CAST (blu as DATE)

and you will get a NUMBER (multiply it by 3600 * 24 and you will turn it into seconds)

BUT you will lose the millisecond info

check this link timestamp difference

Here you have the definition of the TIMESTAMP