Re: TIMESTAMP precision and averaging INTERVAL

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

>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.

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. Received on Fri Nov 09 2007 - 11:34:19 CST