Add together times

Report · Sep 04, 2012

I am wanting to add together a series of stopwatch times, hours mins secs eg 00:10:22 + 01:44:03 = 01:54:25 etc there could be two or 3 or more. They are saved in mySQL as time.

Is this possable? Can be saved as different format if needed.

Cheers

Report · Sep 04, 2012

I think you are better off storing the number of seconds for each record. The time datatype is more appropriate for the time of day.

Report · Sep 04, 2012

Check cflib.org.. there might be a CFC that someone made that will do what you are looking for.

^_^

Report · Sep 07, 2012

thanks for the replies, I have a slightly differnt problem now. I am grouping the times by the days they are made on. I can output the times seperately and they group by days ok but I cant think of a way to add them together. the code below gives me the total of all the times for all days and seems to ignore the grouping even tho it is within the cfoutput grouping tag. getting into and out of seconds seems no problem but Im out of ideas

SELECT date(UserQuiz.dateCompleted) as newdatecompleted, TIME_TO_SEC(userquiz.timespent) AS timespent

FROM userquiz

order by newdatecompleted desc

</cfquery>

<cfoutput> #times.timespent# </cfoutput>

#totaltime#

</cfoutput>

Report · Sep 07, 2012

Look at the sql sum() function and group by clause.

Report · Sep 07, 2012

thanks, I was trying sql sum initialy but it was only bringing up one result, I couldnt work out why. I didnt realize there was a group by in sql. I guess if I use it there I dont need it in cfoutput? Anyway, will play around and see what results I can get

Report · Sep 07, 2012

that works perfectly for the totaltime output but it seems to throw off the cfoutputs within cfoutput

in the example below the timespent output shows the same entry for everyday but Im trying to get it to show the seperate stopwatch entries from that day

SELECT date(UserQuiz.dateCompleted) as newdatecompleted, TIME_TO_SEC(userquiz.timespent) AS timespent, sum(TIME_TO_SEC(userquiz.timespent)) AS sumtotaltime

FROM userquiz

group by newdatecompleted

order by newdatecompleted desc

</cfquery>

<cfoutput query="times"> <cfoutput> #timespent# </cfoutput> #sumtotaltime# </cfoutput>

Report · Sep 07, 2012

Since you are using the ORDER BY clause in your SQL statement you can also group your CF output. See if this works for you:

<cfoutput query="times"> <cfoutput group="newdatecompleted"> #timespent# </cfoutput> #sumtotaltime# </cfoutput>

Report · Sep 08, 2012

tried this but no effect, that was no7

for no8, are you saying that doing this -

SELECT TIME_TO_SEC(userquiz.timespent) AS timespent, sum(TIME_TO_SEC(userquiz.timespent)) AS sumtotaltime

wont give me a return for timespent?

Report · Sep 07, 2012

Regarding:

that works perfectly for the totaltime output but it seems to throw off the cfoutputs within cfoutput

in the example below the timespent output shows the same entry for everyday but Im trying to get it to show the seperate stopwatch entries from that day

You have now changed the requirement from wanting the sum to wanting the individual entries. If you want both, that's possible. One way is to run one database query and join to a subquery on date(datecompleted). The other way is to run two database queries and join them with query of queries.

In theory method 1 should be faster because you only make one trip to the database but in practice, sometimes method 2 is faster. Try both.

Report · Sep 09, 2012

in the end did it by making two queries, one the usual and one using group by and sum, then did a query of queries using both of them

one small detail I havent worked out is how to take the seconds back to time format, at the moment Im using

this works fine but if there is a single digit it only displays one eg 1:1:1 whereas I would prefer to have 01:01:01

thanks for your help, wouldnt have been able to do it otherwise