wood burning stoves 2.0*
The moose likes JDBC and the fly likes SQL Question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL Question" Watch "SQL Question" New topic
Author

SQL Question

Stephen ODonnell
Greenhorn

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,
Thanks,
Stephen.
Rick Portugal
Ranch Hand

Joined: Dec 17, 2002
Posts: 243
Oracle has a new datatype called TIMESTAMP. It is easier than DATE. Check out http://www.dbasupport.com/oracle/ora9i/DATE_TIMESTAMP_Datatypes.shtml


IBM 286, SCJP, SCWCD, EIEIO
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL Question
 
Similar Threads
java.sql.SQLException: ORA-00923: FROM keyword not found where expected
Cannot view data in pop-up window in ascending order
SQL statement to java
SQL string function
ResultSetMetaData problem, not returning data