At 05:12 AM 21/10/2010, you wrote:

>I'm trying to do performance tracing and I'm getting strange numbers. A
>stored procedure that runs for over sixteen seconds is being logged as
>running for 0.0002 seconds.
>
> CREATE TABLE PERF_TRACE
> (
> PROC VARCHAR(32) NOT NULL,
> DURATION DECIMAL(18,4) NOT NULL,
> CONSTRAINT PK_PERF_TRACE PRIMARY KEY (PROC)
> );
>
> PROC DURATION
> --------------
> TEST 0.0000
>
> DECLARE VARIABLE STRT TIMESTAMP;
> ...
> STRT=CAST('NOW' AS TIMESTAMP);
> /* runs for 16.703 seconds */
> UPDATE PERF_TRACE p SET p.DURATION = p.DURATION + (CAST('NOW' AS
>TIMESTAMP) - :STRT) WHERE p.PROC='TEST';
>
> PROC DURATION
> --------------
> TEST 0.0002

>Does anyone have any ideas as to what I could be doing wrong?

The difference between two timestamps returns a result in days. 0.0002 of a day is 17.28 seconds....but the interval calculation corrects to 4 places of decimal. So, for example, if the elapsed time was actually 16.703 seconds, the corrected result in days would be still be 0.0002.

./heLen