We help IT Professionals succeed at work.

Oracle looking to display milliseconds on time subtraction.

Extreme66
Extreme66 asked
on
2,129 Views
Last Modified: 2013-12-18
Hi, i have a simple query that displays the following information, the problem is that it is only showing me seconds. I need to see the time difference in milliseconds. Please help.

select  co.last_update_time, cx.last_update_time, (cx.last_update_time - co.last_update_time)
from tblX co, tblY cx

Results:
16-JUL-08 09.30.31.846000 AM      16-JUL-08 09.30.32.140000 AM      +000000000 00:00:00
16-JUL-08 09.31.08.613000 AM      16-JUL-08 09.31.08.864000 AM      +000000000 00:00:00
16-JUL-08 09.31.46.658000 AM      16-JUL-08 09.31.46.870000 AM      +000000000 00:00:00
16-JUL-08 09.32.06.507000 AM      16-JUL-08 09.32.07.183000 AM      +000000000 00:00:00
Comment
Watch Question

Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
how are you executing and displaying the results?

I've used toad and sql*plus and I get the fractional seconds in both
Shaju KumbalathDeputy General Manager - IT

Commented:
select co.last_update_time, cx.last_update_time, ((((TO_NUMBER(TO_CHAR(cx.last_update_time, 'J')) - TO_NUMBER(TO_CHAR(co.last_update_time, 'J'))) * 86400)+(TO_NUMBER(TO_CHAR(cx.last_update_time, 'SSSSS')) - TO_NUMBER(TO_CHAR(co.last_update_time, 'SSSSS')))))*1000
from from tblX co, tblY cx

Author

Commented:
Im using PL/SQL. Im not sure why it is not displaying the milliseconds.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
shajukq,  that won't work because those functions are for dates and the asker is working with timestamps.

Also, the 3rd column is an interval type.  The fractional seconds should be preserved in it.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
How are you outputting the results in PL/SQL?

dbms_output.put_line?

can you post your procedure?

Author

Commented:
I tried shajukg query, but I only get 1000 for any result
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
yeah,  shajukg's query doesn't apply to your problem at all.  It would be ok if you were working with dates and only wanted a number of seconds between the dates.  But you are, apparently, looking for an interval.  So it simply doesn't apply.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
maybe I'm misunderstanding the problem.

Are you looking to simply "see" the miliseconds on the interval result?  Or are you looking to get a result that is number of milliseconds?  If so,  shajukg's query is the right approach, just wrong datatypes.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
if you are simply looking for total number of seconds, including fractional seconds resolution between two timestamps, you have to use extract on the interval type and add the days,hours,minutes and seconds yourself.'

Try this...
SELECT co.last_update_time, cx.last_update_time,
       (cx.last_update_time - co.last_update_time) x,
           EXTRACT(DAY FROM((cx.last_update_time - co.last_update_time)))
         * 86400
       + EXTRACT(HOUR FROM((cx.last_update_time - co.last_update_time)))
         * 3600
       + EXTRACT(MINUTE FROM((cx.last_update_time - co.last_update_time)))
         * 60
       + EXTRACT(SECOND FROM((cx.last_update_time - co.last_update_time))) d
  FROM tblx co, tbly cx

Open in new window

Author

Commented:
The actual field is type dat in the database
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
It can't be a date type.

dates don't have fractional seconds.

Author

Commented:
Sorry I doublecheckd and it is a timestamp field
Deputy General Manager - IT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
glad I could help everyone!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.