PL SQL :How to show nanoseconds of a TIMESTAMP

I have two timestamps i.e. start= 15-03-11 15:10:10 and finish=15-03-11 15:10:10 and i need to subtract them ("finish-start" ) in order to find the duration. Because开发者_Python百科 their difference is in nanosecond level, i need to convert them into 15:10:10.000000001 and 15:10:10.000000015 respectively.

Depends on how they have been stored - the default setting for TIMESTAMP columns only holds to 6 decimal places Oracle reference so the fields have to be declared TIMESTAMP(9) and if so then the difference will also need to be declared as INTERVAL(9)

Version , platform

First of all, it's about your version and your server platform.(Read OMG Ponies note). Why?

1. Version? If you're using a version older than Oracle 9i, you have no choice but to use "Date" datatype.And you can't manage natively in the language billionth of seconds.

2. Server platform? The time comes from the operating system. If your OS manage billions of seconds you'll be able to manage "Timestamp" with a billionth of granularity.

Esamples: Windows xp or Vista don't let you manage a billionth of second.

Datatypes

If your db platform manages billionth of seconds(as Mark has already pointed out), you can use as datatypes: TIMESTAMP (9) and INTERVAL(quantity of time) Interval day to seconds(9).

To supplement the answers from Zep, Mark, and OMG Ponies - of course, if these values are loaded from some other source (e.g. scientific equipment) that provides a higher degree of precision than what your OS / Oracle version supports, then you can store and manipulate these values as you wish.

For example, store them as a varchar2 and write your own converstion routines. If you have to go that route, be mindfull of things like day rollover etc that your calculation would need to account for.

This worked for me. The idea is to convert the difference to a string and apply regex to retrieve the appropriate parts of the time and convert that part to nanoseconds and at the end add up all the parts. I've done only the seconds (not the hour and minute), but you get the idea.

   /* Formatted on 10/5/2012 5:35:08 PM (QP5 v5.149.1003.31008) */
SELECT TO_NUMBER (
          REGEXP_SUBSTR (TO_CHAR (systimestamp - sysdate),
                         '[0-9]{2}',
                         14),
          '99')
       * 1000000000
       + TO_NUMBER (
            REGEXP_SUBSTR (TO_CHAR ( (systimestamp - sysdate) ),
                           '[0-9]{6}',
                           14),
            '999999')
  FROM dual