Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Oracle SQL to get Timestamp difference

 
amit punekar
Ranch Hand
Posts: 544
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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


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 -


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
Ranch Hand
Posts: 544
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 118
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Amit -
I ran your first query and got a different result :




agad
 
amit punekar
Ranch Hand
Posts: 544
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic