TIMESTAMP precision and averaging INTERVAL

From: Brian Tkatch <N/A>
Date: Fri, 09 Nov 2007 09:56:15 -0500
Message-ID: <6gs8j3lep9sfdi4ubvmnbv2jude5r0lodl@4ax.com>

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. Received on Fri Nov 09 2007 - 08:56:15 CST