Still need avg(timestamp1 - timestamp2), get type error

Thanks VC -

But I have one last problem, I've now realized I need microsecond precision on my calculations (i'm using a timestamp w/ 6 decimal places for the second). I can't seem to find a way to do that. also, is there a way to do an "assignment" (I dont have write privleges to the DB) so I don't have to calculate timestamp1-timestamp2 more than once (if i want to use it a few times in a query)?

Thanks so much!
Ariant

"VC" <boston103_at_hotmail.com> wrote in message news:<c4JNb.77583$xy6.136006_at_attbi_s02>...
> 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 - 17:47:16 CET