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

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.

3 Answers

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

Change '00:00' to INTERVAL '0' DAY:

SELECT COALESCE ( (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')) - (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')), INTERVAL '0' DAY) FROM DUAL; 

More info: Interval Literals

1. With only one expressions to check, COALESCE is not required. NVL would suffice. Basically, if there is no time difference, it would return 0.
2. DATE has a time part, the TIMESTAMP datatype is an extension on the DATE datatype. In addition to the datetime elements of the DATE datatype, the TIMESTAMP datatype holds fractions of a second to a precision between 0 and 9 decimal places, the default being 6. So, in your case, TO_DATE makes more sense.

SQL> SELECT NVL( 2 (to_date( '2014-09-22 16:00:00','YYYY-MM-DD HH24:MI:SS') - to_date('2014-09-22 09:00:00','YYYY-MM-DD HH24:MI:SS')) 3 - (to_date('2014-09-22 16:00:00','YYYY-MM-DD HH24:MI:SS') - to_date('2014-09-22 09:00:00','YYYY-MM-DD HH24:MI:SS')) 4 ,0) DIFF 5 FROM DUAL 6 / DIFF ---------- 0 SQL> 

For other values, to get a significant difference of time interval :

SQL> SELECT NVL( 2 (to_date( '2014-09-22 16:00:00','YYYY-MM-DD HH24:MI:SS') - to_date('2014-09-22 23:00:00','YYYY-MM-DD HH24:MI:SS')) 3 - (to_date('2014-09-22 16:00:00','YYYY-MM-DD HH24:MI:SS') - to_date('2014-09-22 09:00:00','YYYY-MM-DD HH24:MI:SS')) 4 ,0) DIFF 5 FROM DUAL 6 / DIFF ---------- -.58333333 

Update

Since the difference of the dates returns a number, using NVL with TO_DATE won't return interval but a number. As, in above example, it is 0.

To get the interval in the difference, to_timestamp makes sense. So, NVL and TO_TIMESTAMP would be good :

SQL> SELECT NVL ( 2 (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')) - 3 (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')), 4 INTERVAL '0' DAY) diff 5 FROM DUAL 6 / DIFF --------------------------------------------------------------------------- +000000000 00:00:00.000000000