Oracle looking to display milliseconds on time subtraction.

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

Oracle Database

Last Comment

Sean Stuber

8/22/2022 - Mon

Sean Stuber

7/17/2008

how are you executing and displaying the results?

I've used toad and sql*plus and I get the fractional seconds in both

Shaju Kumbalath

7/17/2008

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

Extreme66

7/17/2008

ASKER

Im using PL/SQL. Im not sure why it is not displaying the milliseconds.

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!

James Murphy

Sean Stuber

7/17/2008

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 Stuber

7/17/2008

How are you outputting the results in PL/SQL?

dbms_output.put_line?

can you post your procedure?

Extreme66

7/17/2008

ASKER

I tried shajukg query, but I only get 1000 for any result

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

Sean Stuber

7/17/2008

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 Stuber

7/17/2008

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 Stuber

7/17/2008

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

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

rwheeler23

Extreme66

7/17/2008

ASKER

The actual field is type dat in the database

Sean Stuber

7/17/2008

It can't be a date type.

dates don't have fractional seconds.

Extreme66

7/17/2008