File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
A friendly place for programming greenhorns!
Big Moose Saloon
Register / Login
Win a copy of
EJB 3 in Action
this week in the
EJB and other Java EE Technologies
Joined: Sep 07, 2004
Jan 13, 2005 09:45:00
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:
SELECT SUBSTR(start,1,30) "Time1", SUBSTR(end,1,30) "Time2", SUBSTR((end - start),1,30) "difference", AVG(SUBSTR((end - start),1,30)) AS AvgTime FROM AUDIT_TIME;
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,
Joined: Dec 17, 2002
Jan 13, 2005 14:04:00
Oracle has a new datatype called TIMESTAMP. It is easier than DATE. Check out
IBM 286, SCJP, SCWCD, EIEIO
Joined: Apr 30, 2003
Jan 13, 2005 14:07:00
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:
SELECT SUBSTR(start,1,30) "Time1", SUBSTR(end,1,30) "Time2", SUBSTR((end - start),1,30) "difference", FROM AUDIT_TIME UNION ALL -- (two fake columns to match above columns) SELECT TO_DATE(NULL),TO_DATE(NULL),AVG(SUBSTR((end - start),1,30)) AS AvgTime FROM AUDIT_TIME
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.
It is sorta covered in the
JavaRanch Style Guide
subject: SQL Question
SQL statement to java
SQL string function
ResultSetMetaData problem, not returning data
Cannot view data in pop-up window in ascending order
java.sql.SQLException: ORA-00923: FROM keyword not found where expected
All times are in JavaRanch time: GMT-6 in summer, GMT-7 in winter
| Powered by
Copyright © 1998-2014