jQuery in Action, 2nd edition*
The moose likes Oracle/OAS and the fly likes Oracle SQL to get Timestamp difference Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Oracle SQL to get Timestamp difference" Watch "Oracle SQL to get Timestamp difference" New topic
Author

Oracle SQL to get Timestamp difference

amit punekar
Ranch Hand

Joined: May 14, 2004
Posts: 507
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

Joined: May 14, 2004
Posts: 507
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

Joined: Jan 24, 2006
Posts: 118
Amit -
I ran your first query and got a different result :




agad
amit punekar
Ranch Hand

Joined: May 14, 2004
Posts: 507
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Oracle SQL to get Timestamp difference
 
Similar Threads
using to_timestamp to convert calendar milliseconds?
Performence issue:Write query directly in session bean or call storted procedure
JDBC - Date
Unable to Convert in Timestamp
so I have a sql date format I can't figure out