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

[Ariant]

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

[Jim Kennedy]

"Ariant" <ar******@earthlink.net> wrote in message
news:7a**************************@posting.google.c om...

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

Try the documentation. If you subtract 1 o'clock from 2 o"clock what do you
get? An interval of an hour!
Jim

[VC]

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" <ar******@earthlink.net> wrote in message
news:7a**************************@posting.google.c om...

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

[Ariant]

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" <bo*******@hotmail.com> wrote in message news:<c4JNb.77583$xy6.136006@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" <ar******@earthlink.net> wrote in message
news:7a**************************@posting.google.c om...

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