Re: need avg(timestamp1 - timestamp2), get type error

Hello,

Assuming you have a table like this:

create table t1(x timestamp, y timestamp)

.. the query would be:

select avg (to_date(to_char(x, 'mm/dd/yyyy hh24:mi:ss'), 'mm/dd/yyyy hh24:mi:ss') -

       to_date(to_char(y, 'mm/dd/yyyy hh24:mi:ss'), 'mm/dd/yyyy hh24:mi:ss')) * 24*60*60 avg_seconds
from t1

You need to convert timestamp-> char->date.

Rgds.

VC

"Ariant" <ariant23_at_earthlink.net> wrote in message news:7a108ec3.0401151130.25b50118_at_posting.google.com...
> Hi -
>
> I'm trying to query my database to find the min, max and average times
> (in seconds, or minutes, or something) between two timestamps. I've
> tried using:
>
> Select avg(timestamp1 - timestamp2) from table;
>
> I get an error saying that avg() expects a number, not an interval.
> Is there a way (in a SQL stmt) to convert timestamps (or their
> intervals, if they're different) so that I can run the avg, min, and
> max functions?
>
> I know that I could easily do this in C++ or Java, but I'm hoping to
> do all of my calculations in SQL statements, since they will be faster
> than in Java.
>
> Thanks!
> Ariant
Received on Fri Jan 16 2004 - 04:42:00 CET