SQL Question

[Stephen ODonnell]

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:

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,
Thanks,
Stephen.

[Rick Portugal]

Oracle has a new datatype called TIMESTAMP. It is easier than DATE. Check out http://www.dbasupport.com/oracle/ora9i/DATE_TIMESTAMP_Datatypes.shtml

[Tim McGuire]

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.