Find time difference and average timings

General
joe
Enthusiast

Find time difference and average timings

Hi All,

I have a situation where i have to find the run time and the average run time for a table.

through various post, i came to know that it is bit difficult to find a time differnce and average (simple AVG dosent work here).

Can some one pleae help me here.

Below is the data i have:

StartTime:

5/9/2017 07:20:38.220000

5/8/2017 07:30:48.110000

5/7/2017 07:40:48.100000

EndTime:

5/9/2017 07:21:28.220000

5/8/2017 07:32:38.110000

5/7/2017 07:43:58.100000

My requirement:

1. i need the run time (EndTime-StartTime 'dd:mm:yyyy hh:mm:ss')

2. Need avg run time (hh:mm:ss)

2. Need avg start time (example for last 3 days 'hh:mm:ss')

 

i tried below to get run time. But not in the format i resuire, as given above

1. (((EndTsp))-((StartTsp)) DAY(4) TO SECOND) AS ElapsedTime 

2. (EXTRACT(DAY FROM ElapsedTime) * 1440) + (EXTRACT(HOUR FROM ElapsedTime) * 60) + EXTRACT(MINUTE FROM ElapsedTime) AS MINS --> to get 

 


Accepted Solutions
Apprentice

Re: Find time difference and average timings

Hi Joe,

 

There are a couple of ways that come to mind (and probably others that don't):

Firstly, assume that we have the following calculation:

   ,((firstresptime - starttime) HOUR(2) TO SECOND(6)) AS elapsedtime

You can just use string functions:

   ,OREPLACE(SUBSTRING(CAST( elapsedtime AS CHAR(16)) FROM 2 FOR 8),' ','0')

Which simply uses the appropriate 8 characters from the INTERVAL string.

  • The 'FROM 2' is required because INTERVAL data types always have a space at the front for a +/- sign.
  • The OREPLACE is required because values of less than 10 hours will have a space instead of the first digit, and you wanted your format to have 2 digits for the hour. There are  other ways of doing this bit.

 

Alternatively you can use the following:

   ,(EXTRACT(HOUR FROM elapsedtime) (FORMAT '99'))
     ||':'||(EXTRACT(MINUTE FROM elapsedtime) (FORMAT '99'))
	 ||':'||(EXTRACT(SECOND FROM elapsedtime) (FORMAT '99'))

Do either of those work for you?

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
8 REPLIES
Apprentice

Re: Find time difference and average timings

Hi Joe,

 

I think you probably need to investigate using the INTERVAL data types, although those do have limitations.

 

But first a question. You say that you want the 'run time' formatted as 'dd:mm:yyyy hh:mm:ss'. To me that is a date/time value, i.e. a point in time (efectively a timestamp), it is not a duration. I realise that a run time might be 4 years, 3 months, 16 days, 12 hours etc.

 

If you start with a 'run time' defined using an INTERVAL data type then what the code you've got in your post is probably a good start. This will cater for any run-time up to @9999 days. Is that good enough?

(((EndTsp))-((StartTsp)) DAY(4) TO SECOND) AS ElapsedTime

Once you've got that then you can use AVG on the 'ElapsedTime' column to get an average run time.

 

Your last requirement (average start time) should be something like:

AVG( CAST(SUBSTRING(CAST(starttime AS CHAR(26)) FROM 11 FOR 9) AS INTERVAL HOUR(2) TO SECOND(0)) )

(Yes it looks a bit messy and you might be able to remove one of the CAST functions but it seems to work ok).

 

The possible limitation of INTERVAL data types is that they have a definite upper boundary of accepted values. AN INTERVAL DAY(4) TO SECOND will only accept values up to 9999 days - which is usually good enough for processing. if that isn't then you'll need to convert date/time difference values to seconds and then convert back to a value that is acceptable for display.

 

Does that help?

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
joe
Enthusiast

Re: Find time difference and average timings

Thanks a lot Dave.

I will work on what you said and get back to you.

mean while, below code dosent give me in a good format:

(((EndTsp))-((StartTsp)) DAY(4) TO SECOND) AS ElapsedTime 

can i get the output in hh:mm:ss format??

let me try your other code and see if that suits my requirement.

Thanks a lot again. :) 

Apprentice

Re: Find time difference and average timings

Hi Joe,

You could try:

(((EndTsp))-((StartTsp)) HOUR(2) TO SECOND) AS ElapsedTime 

But please remember that if you have any run time where the elapsed time is greater than 99:59:59 (@4 days) then this query will fail.

 

You could change it to:

(((EndTsp))-((StartTsp)) HOUR(4) TO SECOND) AS ElapsedTime 

which will allow run times up to 9999:59:59 (@416 days). Again, any value higher than this will cause the query to fail.

 

Is that what you want?

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
joe
Enthusiast

Re: Find time difference and average timings

Hi Dave,

 

I tried giving as u said:

(((EndTsp))-((StartTsp)) HOUR(2) TO SECOND) AS ElapsedTime 
(((EndTsp))-((StartTsp)) HOUR(4) TO SECOND) AS ElapsedTime 

but stil the format i am getting is '00:00:00.000000'

can you please suggest on how to exclude the highlighted part above. so thet my final output will in the format "hh:mm:ss:

sample data that i have:

10:22:34.920000

required format:

10:22:34 (i need to ignore .920000)

 thanks a lot in advance.

Apprentice

Re: Find time difference and average timings

Hi Joe,

 

There are a couple of ways that come to mind (and probably others that don't):

Firstly, assume that we have the following calculation:

   ,((firstresptime - starttime) HOUR(2) TO SECOND(6)) AS elapsedtime

You can just use string functions:

   ,OREPLACE(SUBSTRING(CAST( elapsedtime AS CHAR(16)) FROM 2 FOR 8),' ','0')

Which simply uses the appropriate 8 characters from the INTERVAL string.

  • The 'FROM 2' is required because INTERVAL data types always have a space at the front for a +/- sign.
  • The OREPLACE is required because values of less than 10 hours will have a space instead of the first digit, and you wanted your format to have 2 digits for the hour. There are  other ways of doing this bit.

 

Alternatively you can use the following:

   ,(EXTRACT(HOUR FROM elapsedtime) (FORMAT '99'))
     ||':'||(EXTRACT(MINUTE FROM elapsedtime) (FORMAT '99'))
	 ||':'||(EXTRACT(SECOND FROM elapsedtime) (FORMAT '99'))

Do either of those work for you?

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
joe
Enthusiast

Re: Find time difference and average timings

Thanks Dave..

I got what i need.. :)

i really dint think  below menthod:

  ,(EXTRACT(HOUR FROM elapsedtime) (FORMAT '99'))
     ||':'||(EXTRACT(MINUTE FROM elapsedtime) (FORMAT '99'))
	 ||':'||(EXTRACT(SECOND FROM elapsedtime) (FORMAT '99'))

thanks for your patinence too.. 

One last question in this section.

i need an avg of time here.. 

 

i tried to take an AVG(ElapsedTime) though outer query. Query as below:

sel avg(min) from (
sel ((EndTime- starttime) HOUR(2) TO SECOND(6)) AS elapsedtime,

,((EXTRACT(HOUR FROM elapsedtime) (FORMAT '99')) ||':'||(EXTRACT(MINUTE FROM elapsedtime) (FORMAT '99'))) as mins
from table_1 where StartTsp >= current_date-10) a;

please ignore the syntax issue from above query. I wanted to give you an insight on how am i trying to get my avg time.

for above query i am getting below data (for example):

00:04:23

00:05:24

00:04:25

00:03:26

00:04:27

the avg of this is different from what i get from manully taken avg.

Apprentice

Re: Find time difference and average timings

Hi Joe,

 

Generally in SQL I tend to do all of my calculations first using the 'correct' data type and then do any formatting that I require.

 

In this situation, that means:

- (in the derived table) calculate ElapsedTime (as you've done)

- (in the outer query) use the AVG(ElapsedTime) to calculate the average

- (in the outer query) use the EXTRACT function on the result of the AVG to display the answer as you want it.

 

So I think you want something like (I haven't checked the syntax):

sel avg(elapsedtime) as avg_elapsedtime
   ,((EXTRACT(HOUR FROM avg_elapsedtime) (FORMAT '99')) ||':'||(EXTRACT(MINUTE FROM avg_elapsedtime) (FORMAT '99'))) as mins

from (sel ((EndTime- starttime) HOUR(2) TO SECOND(6)) AS elapsedtime
      from table_1 
      where StartTsp >= current_date-10) a;

Try that and see if it gives you what you want.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
joe
Enthusiast

Re: Find time difference and average timings

Thanks Dave.

That works for me now.. :)