473,389 Members | 1,158 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,389 software developers and data experts.

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

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

Similar topics

3
by: Dave | last post by:
Hi I am hoping someone might be able to help me out with this. I am writing a helpdesk system which records agents logging in and out of the system. I need to write a stored procedure which...
4
by: James Bond 007 | last post by:
I am a novice to Javascript (can do simple text-based pop-ups, but not familiar with variable manipulation). I would like to have a Javascript that gives me the start time (I don't care about...
6
by: Mark Reed | last post by:
Hi Guru's, I have created a database to monitor hours I have worked as our payroll department are so crap. I work nights most of the time but occasionally I have to work on days. Between the hours...
2
by: No bother | last post by:
I have a table such as below: BreakID StartTime EndTime 1 08:00:00 10:00:00 2 08:00:00 10:30:00 3 08:00:00 11:00:00 4 08:30:00 11:00:00 What I need to find are the whole clock hours...
1
DTV12345
by: DTV12345 | last post by:
Hello! I am attempting to create a query that calculates a time value from GMT to Pacific time. It is, of course, based on the calendar date that I will have to figure out whether or not...
7
by: fjm | last post by:
Hi all, I have a problem getting a correct result with my sql code and figured I would see if anybody could help. I have two fields in a table that have timestamps. I want to find the...
5
by: clairelee0322 | last post by:
I am a C++ beginner and I am working on a C++ project that calucates the phone bill. I got stuck on how to make every 60 minutes into one hour.. Like if the user enter 1000 (10:00) and it asks for...
1
by: kartouss | last post by:
Hello i am new to this forum...can you help me solve this problem.. I have a function encrypt() which encrypts data from a file in 16 bytes till end of file which in turn calls other 4...
11
by: Connie via AccessMonster.com | last post by:
Hi Access Building Friends, I am building a database for a manufacturer who needs to know the projected End_Date of each job. I know the Start_Date and the total days required to do the job. ...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.