• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Oracle SQL to get Timestamp difference

 
Ranch Hand
Posts: 544
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 118
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
reply
    Bookmark Topic Watch Topic
  • New Topic