Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL Question

 
Stephen ODonnell
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 243
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 820
IntelliJ IDE Tomcat Server VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic