
Calculating times
Hi everyone,<BR><BR>I have a table that displays several time strings showing the amount of time, in this format: HH:MM:SS, that a client was connected to our network.<BR><BR>The amount of rows in this table is not defined as it is determined by the number of times the client has been connected. I wish to add up all the individual connection times to obtain a total online time value.<BR><BR>For example:<BR><BR> Callsign: Name: Time online:<BR> ADY Adrian Foot 1:5:0<BR> ADY1 Adrian Foot 2:0:0<BR> ADY2 Adrian Foot 1:0:0<BR><BR> TOTAL: Adrian Foot 4:5:0<BR><BR>Any help would be appreciated!<BR>Thanks in advance.

you can do that in sql...
select sum(Time_Online) from Table_Name<BR><BR>That will give you the total time.<BR><BR>If you want the total time for each user then, use this sql<BR><BR>select user_name, sum(Time_Online) from Table_Name group by user_name<BR><BR>Please note I have used some dummy name for column and table name, you should change them as per your needs.<BR><BR>~Roy<BR>

RE: you can do that in sql...
No, that won't work.<BR><BR>The data format in the database field is:<BR><BR> H:M:S (e.g. 1:0:5  one hour, 0 minutes and 5 seconds)<BR><BR>You can't add that up in the way you suggest....I wish it was that easy :)<BR><BR><BR>Adrian

that may not be this straight forward...
Here is another try.<BR><BR>I didn't consider cases where the total minutes or seconds exceed 60. For example, if the total time is,<BR><BR>10:120:120<BR><BR>which is 10 hrs, 120 mins, 120 seconds. You will obviusly want it to be 12 hrs, 2 mins, 0 seconds, right?<BR><BR>So we got a little conversion to do here.<BR><BR>Anyway you can still use the query I posted in my earlier post and get the total time on the ASP page. Now let us call the total time as Sum_Time.<BR><BR>So for example total time could be,<BR><BR>Sum_TIme = 10:120:120<BR><BR>Now we can do a split,<BR>Split_Time = Split(Sum_TIme,":")<BR><BR>So now, <BR>Split_Time(0) = 10<BR>Split_Time(1) = 120<BR>Split_Time(2)= 120<BR><BR>Now do the conversion,<BR>'If number of seconds is more than 60 then get the minutes and seconds out of it by dividing by 60<BR>If Split_Time(2) > 60 Then <BR> V_Minutes = (Split_Time(2))/60<BR> V_Seconds = (Split_Time(2))%60<BR> Split_Time(2) = V_Seconds<BR>End If<BR>'Add the number of minutes from above to the number of minutes in Split_Time(1)<BR><BR>'If number of minutes is more than 60 then get the hours and minutes out of it by dividing by 60<BR>Split_Time(1) = Split_Time(1) + V_MInutes<BR>If Split_Time(1) > 60 Then<BR> V_Hours = (Split_Time(1))/60<BR> V_Minutes = (Split_Time(2))%60<BR> Split_Time(1) = V_Minutes<BR>End If<BR>'Add the number of hours from above to the number of hours in Split_Time(0)<BR>Split_Time(0) = Split_Time(0) + V_Hours<BR><BR>Now you can join them to get the time,<BR>Sum_Of_TIme = Split_Time(0) & ":" & Split_Time(1) & ":" & Split_Time(2)<BR><BR>Well that code has got a little lengthy, but you would have got the idea.<BR><BR>~Roy

I didn't mention....
....that the server automatically converts so that if minutes >60 it will make minutes = 0 and add 1 onto the hours total.

Addendum...
Let the SQL just be...<BR><BR>sql = "select some_time from table_name"<BR><BR>Create a recordset with this sql.<BR><BR>rs(some_time) will have time values. So now do a split and run a loop to add the time.<BR><BR>ArrRS = rs.GetRows()<BR><BR>For i = 1 to UBound(ArrRS)<BR> Split_Time = Split(ArrRS(i),":")<BR> Total_Hours = Split_Time(0)<BR> Total_Minutes = Split_Time(1)<BR> Total_Seconds = Split_Time(2)<BR>Next<BR><BR>So now you have both the parts from this post and my earlier post. Join the two and you will have your answer.

hehe  whoops
Sorry  I'm a little tired....I'm what you've said will all work (it makes sense)  I'm just about to test it and I'll get back to you....sorry about my stupidity there...

RE: Addendum...
Thanks....that looks like it makes sense...<BR><BR>...but could I ask a favour? I have played for a bit and tried to get it all working together (by peicing the code together with this new code you have given me) but I can't get it working :S....<BR><BR>Could you put it together for me please?

Okay ...here it is
sql = "select some_time from table_name" <BR><BR>Create a recordset with this sql. <BR><BR>rs(some_time) will have time values. So now do a split and run a loop to add the time. <BR><BR>ArrRS = rs.GetRows() <BR><BR>For i = 1 to UBound(ArrRS) <BR> Split_Time = Split(ArrRS(i),":") <BR> Total_Hours = Split_Time(0)<BR> Total_Minutes = Split_Time(1) <BR> Total_Seconds = Split_Time(2)<BR>Next <BR><BR>Now do the conversion, <BR>'If number of seconds is more than 60 then get the minutes and seconds out of it by dividing by 60 <BR>If Total_Seconds > 60 Then <BR> V_Minutes = (Total_Seconds)/60 <BR> V_Seconds = (Total_Seconds)%60 <BR> Total_Seconds = V_Seconds <BR>End If <BR>'Add the number of minutes from above to the number of minutes in Total_Minutes <BR><BR>'If number of minutes is more than 60 then get the hours and minutes out of it by dividing by 60 <BR>Total_Minutes = Total_Minutes + V_MInutes <BR>If Total_Minutes > 60 Then <BR> V_Hours = (Total_Minutes)/60 <BR> V_Minutes = (Total_Seconds)%60 <BR> Total_Minutes = V_Minutes <BR>End If <BR>'Add the number of hours from above to the number of hours in Total_Hours <BR>Total_Hours = Total_Hours + V_Hours <BR><BR>'Now you can join them to get the time, <BR>Sum_Of_TIme = Total_Hours & ":" & Total_Minutes & ":" & Total_Seconds <BR><BR>Again there might be other ways which will require less coding than this. But this is one of the ways to do it.

Ummm....no that doesn't work.....
I tried that and it doesn't work...<BR><BR>Microsoft VBScript runtime error '800a0009' <BR><BR>Subscript out of range: 'ArrRS' <BR><BR>/test.asp, line 14 <BR><BR>I'm sure it would be easier to do it somehow like this:<BR><BR>Parse through the records and add all the values together so you have the total number of SECONDS that the member has been connected for. This means splitting the field (that starts in the format of H:M:S) so that it is JUST Seconds. Once we have the total seconds we can convert it back to the full field again....this looks much easier to do but I just don't know how to do it.
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

