Android Question Summing times

[harinder]

Hello..I have a series of lapsed times like 02:00
05:45
10:35
13:45
I want to add them up and result should be 32:05..These times are a part of sql db and I have a separate column where each are converted to strings..after passing the sum query I get the total ticks..issue is that after resetting timezone to 0 and doing datetime.time on the sum, I get minutes correctly as 05 but hours are 08, as hh resets after 24. Can somebody suggest time format to display unlimited hours and min limited to 60? Thanks..

 

[klaus]

Have a look at the DateUtils library.

 

[harinder]

Hello klaus..im sorry I have not been able to find the answer..i need to extract only hours and minutes from a sum of ticks, with minutes limited to 59 and limitless hours..i remember there is a format in excel [h]:mm which does this..how can i achieve this in b4a? Thnx..

 

[Erel]

If you need to reset the timezone to 0 then you are doing something wrong.

I assume that you are using DateTime.Time to convert the total number of milliseconds to string. This is a mistake. You should only use DateTime.Time to convert ticks to string. Ticks = Number of milliseconds since 1/1/1970 00:00 UTC.

Use this code instead:

Sub ConvertMillisecondsToString(t As Long) As String
   Dim hours, minutes, seconds As Int
   hours = t / DateTime.TicksPerHour
   minutes = (t Mod DateTime.TicksPerHour) / DateTime.TicksPerMinute
   seconds = (t Mod DateTime.TicksPerMinute) / DateTime.TicksPerSecond
   Return $"$1.0{hours}:$2.0{minutes}:$2.0{seconds}"$
End Sub

 

[harinder]

Thank you Erel..actually this is part of a larger calculation where I have used time and datedialogs to input times in UTC..therefore timezone had to be set to 0 for lapsed time sum calculations otherwise an error equal to ZT was appearing in final result..

Alternatively,I have now used sql to make separate columns for hours and minutes using stringfunctions.mid, summing the hours and minutes separately and then the following routine:

For i= 1 to 100
  If summin > 60*i And summin < 60+(60*i) Then
  sumhr = sumhr + i
  summin = summin-60*i
  End if
Next

Only thing is if total minutes exceed 6000 then above will fail.
Can you recommend a better routine covering all possibilities?
Thank you..

 

[Erel]

therefore timezone had to be set to 0 for lapsed time sum calculations otherwise an error equal to ZT was appearing in final result

All that I can say is that if you needed to reset the time zone then you were doing something wrong.

I don't understand the code you posted. What exactly are you trying to calculate?

 

[Emme Developer]

All that I can say is that if you needed to reset the time zone then you were doing something wrong.

I don't understand the code you posted. What exactly are you trying to calculate?

I think he's tryng to add different hours, and he splits hours and minutes in 2 columns on sql. He should use Period class in dateutils to add and get the hours and minutes, instead make a method to get hours and minutes from a text field in db

 

[harinder]

Ok Emme..Can you please elaborate how to use period class of dateutils to add many elapsed times?
For eg adding
02:30
05:30
12:45
03:25
04:45
So that result is 28:55 and NOT 04:55..
Thnx..

 

[KMatle]

So you just need to add hours and minutes? Then you will not need to use any DateUtils methods because one day has only 24 hours. You can't use any datetime functions here.

1. add all the hours = 26
2. add all the minutes = 175
3. get the mod of 175/60 -> 175 mod 60 = 55 minutes (rest)
4. get the hours 175 - 175 mod 60 / 60 = 2
5. add 2 hours to 26 = 28
6. minutes = 55
7. 28:55

OR

26 hours * 60 = 1560 minutes plus 175 minutes = 1735
1735 - 1735 mod 60 / 60 = 28 hours
1735 - 28*60 = 55 minutes

 

[Mahares]

So that result is 28:55 and NOT 04:55..

When you extract the data from your SQLite table, you can build a list and use a simple Regex function as shown in the below example. You do not need any DateUtils:

Dim MyList As List
    MyList.Initialize
    MyList.AddAll(Array As String("02:30", "05:30", "12:45", "03:25", "04:45"))
    Dim h , m As Int
    For i=0 To MyList.Size-1
        Dim hm() As String =Regex.Split(":",MyList.Get(i))
        h=h+hm(0)
        m=m+hm(1)   
    Next
    h=h+Floor(m/60)
    m=m Mod 60
    Log($"${h}:${m}"$)  'will display 28:55

 

[Erel]

Another version:

Dim MyList As List = Array ("02:30", "05:30", "12:45", "03:25", "04:45")
Dim ms As Long
For Each tim As String In MyList
   Dim hm() As String =Regex.Split(":", tim)
   ms = ms + hm(0) * DateTime.TicksPerHour + hm(1) * DateTime.TicksPerMinute
Next
Log(ConvertMillisecondsToString(ms)) 'from post #4 (you can easily change the string pattern if you don't want the seconds).

 

[harinder]

Dear Klaus,Erel,Emme,KMatle and Mahares,
Thank you for your efforts in solving this issue for me.
As I am using a db, I find it comfortable to filter data using sql between today and last 7 days as in example below. Hours and Minutes are in separate columns and added separately as sumdayhr and sumdaymin and then sorted by the Mod routine to display total as hh:mm.

          Dim sevendays As Long = DateTime.Add(DateTime.Now, 0, 0, -7)
        Dim mainquery As String
        mainquery = "SELECT  *  FROM " & Main.DBTableName &" WHERE chksoffticks between "&sevendays _
        &" And  "&DateTime.now&" order by chksoffticks"
 
        Dim dayhr As String
        dayhr = "SELECT SUM(dayhr)  FROM " & Main.DBTableName &" WHERE chksoffticks between  "&sevendays&" And  "&DateTime.now& " "
        Dim sumdayhr As String=Main.sql1.ExecQuerySingleResult(dayhr)
     
        Dim daymin As String
        daymin = "SELECT SUM(daymin)  FROM " & Main.DBTableName &" WHERE chksoffticks between  "&sevendays&" And  "&DateTime.now& " "
        Dim sumdaymin As String=Main.sql1.ExecQuerySingleResult(daymin)

        sumdayhr=sumdayhr+ Floor(sumdaymin/60)
        sumdaymin=sumdaymin Mod 60

It is working well. I am just a nube in B4a(2 months old) without any previous programming experience and find b4a a very practical way of learning to make apps. Hats off to b4a team and you are doing an excellent work.

 

[Mahares]

Dim sevendays As Long = DateTime.Add(DateTime.Now, 0, 0, -7)

1. Since you are using BETWEEN in your SQLite query, the date range includes both outer numbers . Therefore this:

Dim sevendays As Long = DateTime.Add(DateTime.Now, 0, 0, -7)

Should be this:

Dim sevendays As Long = DateTime.Add(DateTime.Now, 0, 0, -6)

Otherwise, you are including 8 days in your query when you really want 7 days.
2. Your code is great, but @Erel always recommends parameterized queries instead.
3. Although your queries are correct, your 2nd and 3rd query in your above post can also be combined in one query with concatenation. Something similar to this:

Dim MyQuery As String
    MyQuery="SELECT SUM(dayhr) ||':'|| SUM(daymin)  FROM " & Main.DBTableName & " WHERE chksoffticks between  ? And  ?"
    Dim sumdayhrmin As String=Main.sql1.ExecQuerySingleResult2(MyQuery, Array As Object(sevendays, DateTime.now))

 

[harinder]

Dear Mahares,
Thanks for the heads up on the way two filter queries can be concatenated.
But I need to keep both hr and min totals separate for this mod:

sumdayhr=sumdayhr+ Floor(sumdaymin/60)
sumdaymin=sumdaymin Mod60

and then get them together after sorting as hr:min...Rgds...

 

[Mahares]

But I need to keep both hr and min totals separate for this mod:

Hi @harinder
That is true, but then with my one query you parse the string with RegEx (see below). Suppose you get this from the query I posted: sumdayhrmin ="234:198"

 Dim sumdayhr, sumdaymin As Int
    Dim hm() As String =Regex.Split(":",sumdayhrmin )
    sumdayhr=hm(0)
    sumdaymin=hm(1)
    sumdayhr=sumdayhr+ Floor(sumdaymin/60)
    sumdaymin=sumdaymin Mod 60
    Log($"${sumdayhr}:${sumdaymin}"$)  'will print 237:18

Best Regards

 

[harinder]

Fantastic..or maybe use stringfunctions

  Dim sf as stringfunctions
  sumdayhr=sf.mid(sumdayhrmin,1,2)
  sumdaymin=sf.mid(sumdayhrmin,4,2)
  sumdayhr=sumdayhr+ Floor(sumdaymin/60)
  sumdaymin=sumdaymin Mod60
  Log($"${sumdayhr}:${sumdaymin}"$)

 

[harinder]

not above one..i assumed hr and min are 2 digits each. This one works:

Dim MyQuery As String
        Dim sf As StringFunctions
        Dim sumdayhr,sumdaymin As Int
        sumdayhr=sf.SplitGetWord(sumdayhrmin,":",1)
        sumdaymin=sf.SplitGetWord(sumdayhrmin,":",2)
        sumdayhr=sumdayhr+ Floor(sumdaymin/60)
        sumdaymin=sumdaymin Mod 60

 

[Mahares]

Dim sf As StringFunctions

It is not recommended to use StringFunctions library as it is has not been supported for a long time. You have enough tools in the Core lib of B4A.
https://www.b4x.com/android/forum/t...ary-generates-error-in-b4a-version-6-8.76943/

 

[harinder]

Noted Mahares..Thank you for the caution...