This week's giveaway is in the Spring forum.
We're giving away four copies of REST with Spring (video course) and have Eugen Paraschiv on-line!
See this thread for details.
The moose likes JDBC and Relational Databases and the fly likes SQL Question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of REST with Spring (video course) this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "SQL Question" Watch "SQL Question" New topic

SQL Question

Stephen ODonnell

Joined: Sep 07, 2004
Posts: 19
Hi all,

I'm new to sql and need some help. I'm trying to subtract the difference between two timestamps in an oracle DB, then find average of the time difference. Heres what I've come up with so far:

This does not work because:
I think I need to convert 'difference' to seconds. How can do this?

Also if anyone can recommend a good website to learn sql, please do so.

Any help greatly appreciated,
Rick Portugal
Ranch Hand

Joined: Dec 17, 2002
Posts: 243
Oracle has a new datatype called TIMESTAMP. It is easier than DATE. Check out

Tim McGuire
Ranch Hand

Joined: Apr 30, 2003
Posts: 820

It seems like your query has an aggregate function in line with the rest of the query. That is, you don't want the average for the entire table to appear on every row, do you?

I often do a union like this to get averages or totals at the end of a query:

this is two queries in one. the first one gets the rows, the second one the average.

Use UNION ALL instead of UNION because UNION checks to make sure there are no duplicates between the two queries. UNION ALL tells it to skip this check and it is much faster.
Consider Paul's rocket mass heater.
subject: SQL Question
jQuery in Action, 3rd edition