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

[richardson]

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.

[chandu031]

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:

 
SELECT job_name , AVG(
          TO_NUMBER(substr((END_TIME-START_TIME),instr((END_TIME-START_TIME),' ')+10,3)/ 1000) +                
          TO_NUMBER(substr((END_TIME-START_TIME),instr((END_TIME-START_TIME),' ')+7,2) ) +               
         TO_NUMBER(substr((END_TIME-START_TIME),instr((END_TIME-START_TIME),' ')+4,2)) *60 +     
         TO_NUMBER( substr((END_TIME-START_TIME),instr((END_TIME-START_TIME),' ')+1,2))*3600
            )
     FROM rdbdev.loop_through_job_stats 
WHERE report_id =12059909 
GROUP BY job_name 
 
 

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.