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(en d_time - start_time) FROM rdbdev.loop_thr ough_job_stats WHERE report_id =12059909 GROUP BY job_name

the avg returned returns in an exponentiation values like

9.2592592592592 6E-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(en d_time - start_time) FROM rdbdev.loop_thr ough_job_stats WHERE report_id =12059909 GROUP BY job_name

the avg returned returns in an exponentiation values like

9.2592592592592 6E-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:

[code=sql]

SELECT job_name , AVG(
TO_NUMBER(subst r((END_TIME-START_TIME),ins tr((END_TIME-START_TIME),' ')+10,3)/ 1000) +
TO_NUMBER(subst r((END_TIME-START_TIME),ins tr((END_TIME-START_TIME),' ')+7,2) ) +
TO_NUMBER(subst r((END_TIME-START_TIME),ins tr((END_TIME-START_TIME),' ')+4,2)) *60 +
TO_NUMBER( substr((END_TIM E-START_TIME),ins tr((END_TIME-START_TIME),' ')+1,2))*3600
)
FROM rdbdev.loop_thr ough_job_stats
WHERE report_id =12059909
GROUP BY job_name

[/code]

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.