Oracle SQL to get Timestamp difference

[amit punekar]

Hello,
I am trying to write a SQL that will give me the difference between two timestamps. The Datatype of the column is TIMESTAMP.

The query is as below
select (TO_TIMESTAMP('7/26/2011 05.26.21.596788000 AM','MM/DD/YYYY HH:MI:SS:FF AM') - TO_TIMESTAMP('7/26/2011 05.26.21.504439000 AM','MM/DD/YYYY HH:MI:SS:FF AM')) from dual;

This gives me the result as "0 0:0:0.92349000".



If we just take seconds and FF part of the above and do the numeric calculation as in SQL below -
SELECT (21.596788000 - 21.504439000) from dual;

This does return me "0.09" which appears to be correct.

I understand that second SQL is numeric substraction whereas the firstone is Timestamp one. But the difference between miliseconds should be consistent. Isnt it?

Or is there anything that I am not able to understand.

Thanks and Regards,
Amit

[amit punekar]

Hello Admin,
Can you please help move this topic to JDBC forum as well. I might get some help over there.

Regards,
Amit

[Agador Paloi]

Amit -
I ran your first query and got a different result :

(TO_TIMESTAMP('7/26/201105.26.21.596788000AM','MM/DD/YYYYHH:MI:SS:FFAM')-TO_TIMESTAMP('7/26/201105.26.21.504439000AM','MM/DD/YYYYHH:MI:SS:FFAM')) 
------------------------------------------------------------------------------------------------------------------------------------------------- 
0 0:0:0.092349                                                                                                                                    

agad

[amit punekar]

Hi Agad,
Thanks for your kind reply.
What is the Oracle version you are using?
I tried this on Oracle 11g and did not get the zero after decimal.
I have resolved this by typecasting the result to CHAR using TO_CHAR which preserved the zero after the decimal point.

Regards,
Amit