Oracle 9.2.0.8.0

1) Oracle has a precision on TIMESTAMP with a default precision of 6.
Selecting 9 always returns three more zeroes. Why?

09:45:35> SELECT SYSTIMESTAMP(9) FROM Dual;

SYSTIMESTAMP(9)
---------------------------------------------------------------------------
09-NOV-07 09.46.44.186794000 AM -05:00

09:46:44> /

SYSTIMESTAMP(9)
---------------------------------------------------------------------------
09-NOV-07 09.46.45.146996000 AM -05:00

09:46:45> /

SYSTIMESTAMP(9)
---------------------------------------------------------------------------
09-NOV-07 09.46.45.658828000 AM -05:00

2) I have a TABLE i am using for profiling (while debugging):

CREATE TABLE Profile
(
Id INT,
What VARCHAR2(64),
Line INT,
When TIMESTAMP DEFAULT SYSTIMESTAMP,
Diff INTERVAL DAY TO SECOND,
CONSTRAINT P1_Id_NN CHECK(Id IS NOT NULL),
CONSTRAINT P1_Id_PK PRIMARY KEY (Id)
);

The Id is put in by a TRIGGER/SEQUENCE pair, and Diff is an UPDATE:

UPDATE
Profile A
SET
Diff = When -(SELECT When
FROM Profile B
WHERE B.Id = A.Id -1);

I want to get the average interval for similar Lines (across "What"s),
something like:

SELECT
Line,
AVG(Diff)
FROM
Profile
GROUP BY
Line;

But, obviously, AVG() does not work on INTERVALs. Is there a built-in
method for averaging INTERVALs, or would i need to calculate the
seconds and microseconds myself. And, if the latter, does INTERVAL
even make sense?

B.

On Nov 9, 9:56 am, Brian Tkatch <N/A> wrote:
The fractional time portion of the timestamp data type is platform
dependent. Different hardware platforms only support n digits so the
same Oracle code on a Linux box may produce a different value from a
Sun box which in turn could be different from what is returned under
IBM's AIX.

In other words it is a hardware timer availability issue. Oracle uses
what the vendor provides.

HTH -- Mark D Powell --

On Nov 9, 11:50 am, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote:
PS - There is also the issue that the default is 6 digits of accuracy
so even on a platform that can provide 9 digits of accuracy if the
insert into the table defaulted to six digits then you can only
retrieve the value that was stored.

HTH -- Mark D Powell --

On Fri, 09 Nov 2007 08:55:03 -0800, Mark D Powell
<Mark.Powell (AT) eds (DOT) com> wrote:

Thanx! I figured it was platform dependent, but i wanted confirmation.
The information on the accuracy of statements is quite interesting
too.

B.

On Fri, 09 Nov 2007 09:56:15 -0500, Brian Tkatch <N/A> wrote:


CREATE OR REPLACE FUNCTION TO_FF(IN_A TIMESTAMP, IN_B TIMESTAMP)
RETURN NUMBER
DETERMINISTIC
AS BEGIN
RETURN (TO_CHAR(IN_B, 'SSSSS') + TO_CHAR(IN_B, 'FF') * .000001)
- (TO_CHAR(IN_A, 'SSSSS') + TO_CHAR(IN_A, 'FF') * .000001);
END;
/

I'm not debugging past midnight so i took a shortcut. Does this seem
correct?

B.