473,389 Members | 1,158 Online

# 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
1 3989
chandu031
78 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