By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,501 Members | 1,861 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,501 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Jul 19 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a

"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
Jul 19 '05 #2

P: n/a
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

Jul 19 '05 #3

P: n/a
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

Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.