467,859 Members | 1,361 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,859 developers. It's quick & easy.

calculate the avg time of two time stamps like start_time and end_tiem

Hi all i need ur help on how to calculate the avg time of two time stamps like start_time and end_time .

The ideal need is to know the average time taken by each job from a table .

i tried
SELECT job_name,AVG(end_time - start_time) FROM rdbdev.loop_through_job_stats WHERE report_id =12059909 GROUP BY job_name

the avg returned returns in an exponentiation values like

9.25925925925926E-7

in some cases ...how to get the correct value in minutes ...pls suggest.....

that is a paticular job had taken zzz minutes on a na average to complete the report.
May 23 '07 #1
  • viewed: 3755
Share:
1 Reply
chandu031
Expert
Hi all i need ur help on how to calculate the avg time of two time stamps like start_time and end_time .

The ideal need is to know the average time taken by each job from a table .

i tried
SELECT job_name,AVG(end_time - start_time) FROM rdbdev.loop_through_job_stats WHERE report_id =12059909 GROUP BY job_name

the avg returned returns in an exponentiation values like

9.25925925925926E-7

in some cases ...how to get the correct value in minutes ...pls suggest.....

that is a paticular job had taken zzz minutes on a na average to complete the report.

Hi Richardson,

Here's a sample query:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT job_name , AVG(
  3.           TO_NUMBER(substr((END_TIME-START_TIME),instr((END_TIME-START_TIME),' ')+10,3)/ 1000) +                
  4.           TO_NUMBER(substr((END_TIME-START_TIME),instr((END_TIME-START_TIME),' ')+7,2) ) +               
  5.          TO_NUMBER(substr((END_TIME-START_TIME),instr((END_TIME-START_TIME),' ')+4,2)) *60 +     
  6.          TO_NUMBER( substr((END_TIME-START_TIME),instr((END_TIME-START_TIME),' ')+1,2))*3600
  7.             )
  8.      FROM rdbdev.loop_through_job_stats 
  9. WHERE report_id =12059909 
  10. GROUP BY job_name 
  11.  
  12.  
This will give you the result in seconds.
As you can probably guess , I am extracting different parts of the timestamp and then adding it up.Note that the first extract is that of milliseconds.

Try it out and see if it works for you.
May 24 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Dave | last post: by
4 posts views Thread by James Bond 007 | last post: by
6 posts views Thread by Mark Reed | last post: by
2 posts views Thread by No bother | last post: by
1 post views Thread by kartouss | last post: by
11 posts views Thread by Connie via AccessMonster.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.