Get the average of multiple times with coldfusion

I am using the below method to get the time difference between 2 times and output it. This will output a bunch of records.

I need to get the average time difference, can someone assist? I'd like to display the average time difference in format 00:00:00:00 (that is days:hours:minutes:seconds).  Thank you!

<cfoutput query="qTheQuery">
<cfset dtFrom = #qTheQuery.Created# />
<cfset dtTo =  #qTheQuery.Completed# />
<cfset sec=DateDiff("s",dtFrom,dtTo)>
<cfset days=int(sec/86400)>
<cfset hours=int((sec-(days*86400))/3600)>
<cfset minutes=int((sec-(days*86400)-(hours*3600))/60)>
<cfset seconds=(sec-(days*86400)-(hours*3600)-(minutes*60))>
<cfset days=numberFormat(  days, "00") >
<cfset hours=numberFormat( hours, "00") >
<cfset minutes=numberFormat( minutes, "00") >
<cfset seconds=numberFormat( seconds, "00") >
	
<tr>
	<td>#days#:#hours#:#minutes#:#seconds#
	</td>
</tr>	
</cfoutput>
</tbody>
</table>

Open in new window

earwig75Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gdemariaCommented:
Use the total number of seconds, I think "sec" is the variable and then add them up.

<cfset totalSec = totalSec + sec>

At the same time, count the number of iterations so you know what to divide by
<cfset counter = counter + 1>

Then, just take the average

 <cfset averageSec = totalSec / counter>

Once you have the average seconds, you can split the results out into hours, minutes, etc as you are already doing
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
earwig75Author Commented:
are you recommending putting that right in the cfoutput query?
0
_agx_Commented:
(no points please .. I'm just repeating what GD's already said)

Yes.  GD's saying you can calculate the total number of seconds as you loop:

<cfset totalSec = 0>
<cfoutput query="qTheQuery">
     .... current code here ...

    <!--- append number of seconds to total --->
    <cfset totalSec = totalSec + sec>
</cfoutput>

Then use it to calculate the average and format it the same way.  One minor improvement. You probably don't need a counter. Use the query recordCount.  

<!--- calculate average / avoid divide by 0 error --->
<cfset averageSec =  qTheQuery.recordCount ? totalSec / qTheQuery.recordCount : 0>
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

earwig75Author Commented:
This is how I have it, and it appears to be working now, thank you. @ _agx_, I am confused about what you're doing to avoid dividing by zero. Are you using a different method of getting the counter there?

This is what I'm using.
	<cfset totalSec = "0">
	<cfset counter = "0">
<cfoutput query="qtimeDiffResults">
	<cfset timeDiffTime="0">
	<cfset dtFrom = #qtimeDiffResults.Created# />
	<cfset dtTo =  #qtimeDiffResults.Finished# />
	<cfset sec=DateDiff("s",dtFrom,dtTo)>
	<cfset days=int(sec/86400)>
	<cfset hours=int((sec-(days*86400))/3600)>
	<cfset minutes=int((sec-(days*86400)-(hours*3600))/60)>
	<cfset seconds=(sec-(days*86400)-(hours*3600)-(minutes*60))>
	<cfset days=numberFormat(  days, "00") >
	<cfset hours=numberFormat( hours, "00") >
	<cfset minutes=numberFormat( minutes, "00") >
	<cfset seconds=numberFormat( seconds, "00") >

	<cfset totalSec = totalSec + sec>
	<cfset counter = counter + 1>
	<cfif counter neq 0>
	<cfset averageSec = totalSec / counter>
         </cfif>

Open in new window

0
gdemariaCommented:
This part needs to be outside your loop, you only want to get the average after you loop through everything..
The CFIF below prevents the divide by zero
  <cfif counter neq 0>
	<cfset averageSec = totalSec / counter>
   </cfif>

Open in new window

0
gdemariaCommented:
To explain for agx, this cool format is like a condensed IF ELSE statement.

<cfset  answer =   Test ? Then : Else >

So this says (Test) if recordCount (is true which is non-zero) ? then do the division : else show 0

<cfset averageSec =  qTheQuery.recordCount ? totalSec / qTheQuery.recordCount : 0>
0
earwig75Author Commented:
Great, thank you for the help today guys!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.