Android Question Time calculations problems

[Roger C]

Hi,

I have a database with (among other) following fields; StartTime, StartDate, EndTime, EndDate. (In the code below, called InTid, InDatum, UtTid and UtDatum)
Now I want to add the time difference between StartTime and EndTime on posts with the same date or week or all posts....

I use following code (much reduced regarding selection, reading DB and so on, but this is the important code for the timecalculations):

Dim totaltime, presenttime As Long

totaltime = 0
presenttime = 0

DateTime.TimeFormat = "HH:mm"

DateTime.DateFormat="yyyy-MM-dd"

For i = 0 To crnames.RowCount
    crnames.Position = i
    If crnames.GetString("uttid") <> "00:00:00" Then
        totaltime=DateTime.DateTimeParse(crnames.GetString("utdatum"), _
crnames.GetString("uttid"))-DateTime.DateTimeParse(crnames.GetString("indatum"), _
crnames.GetString("intid"))+totaltime

    Else
        presenttime=DateTime.now - DateTime.DateTimeParse(crnames.GetString("indatum"), _
crnames.GetString("intid"))

    End If
Next

If presenttime = 0 Then

    Text = "Total time: " & DateTime.Time(totaltime)
Else

    Text = "Total time: " & DateTime.Time(totaltime) & " + " & DateTime.Time(presenttime) & _
" (" & DateTime.time(totaltime + presenttime) & ")"
End If

Presenttime is used for an open timeframe that hasn't been ended yet (time = 00:00:00) so I calculate with current time, that is why it ends up in brackets when displayed at the end.

When running this in the emulator it all works fine, even between different dates....

...but when running it in the phone I always get one hour to much.

In the emulator totaltime is (for example) 4:23 and presenttime is 1:25 and adds up to 5:48.
In the phone totaltime gets 5:23, presenttime gets 2:25 and they adds up to 6:48.

As you see all time presented is one hour to much, also the added time which should be two hors more instead.
So the problem is in 'DateTime.Time(totaltime)' conversion.

But why???? And only in the phone?

 

[DonManfred]

Try using "Period" from DateUtils.

See this example i wrote a few days ago for one forumuser...

Dim last_time As String = "23/05/2013"
Dim Y, T As Long
DateTime.DateFormat = "dd/MM/yyyy"
DateTime.TimeFormat = "HH:mm:ss"

Y= DateTime.DateTimeParse(last_time,"00:00:01")
T= DateTime.DateTimeParse(DateTime.Date(DateTime.Now),"00:00:01")
Dim diff As Period
diff = DateUtils.PeriodBetween(Y,T)
Log("Diff="&diff.Years&" years, "&diff.Months&" months, "&diff.Days&" days, "&diff.Hours&" hours, "&diff.Minutes&" minutes, "&diff.Seconds&" seconds")

 

[eps]

Time zone?

http://www.b4x.com/android/forum/threads/how-to-use-timezone.33872/#post-198605

 

[Roger C]

Thanks Manfred and eps.

Ok, tried your suggestion Manfred and the emulator still counts correctly.

Used this code.

diff = DateUtils.PeriodBetween(DateTime.DateTimeParse(crnames.GetString("indatum"), crnames.GetString("intid")),DateTime.DateTimeParse(crnames.GetString("utdatum"), crnames.GetString("uttid")))
If diff.Days > 0 Then diff.Hours = diff.Hours + (24 * diff.Days)
thours = thours + diff.Hours
tmins = tmins + diff.Minutes

Haven't tried it in the phone yet.
But how do I really handle diff.hours and diff.minutes?
Adding them like above works but then at the end I have to take care when the minutes exceeds 60. Are there any function in DateUtils or DateTime to handle the adding?

Should i use

totaltime = DateUtils.AddPeriod(totaltime, diff)

instead?

The problem is not really in the adding since it seems to count correctly also in my first post.
I'm still left with ticks that has to be converted to displaytime like '00:00'.

I've changed the timezone in the emulator to the correct (+2 GMT for Stockholm, was +0 GMT before).
Now the emulator counts one hour off and not two as I supposed it would...

Can't I just turn off timezonecorrections? I just need ticks for hours and minutes to be converted to '00:00'?

I will try and change my code where necessary and load it in my phone too to see if it affects the calculations there...

 

[Roger C]

Been checking this in the emulator now, can't load it in the phone today needs it tomorrow.

Used

totaltime = DateUtils.AddPeriod(totaltime, diff)

The variable totaltime sums up to 26.520.000 ticks, a manually count gives 7 hours and 22 minutes and this is correct.

The function

DateTime.Time(totaltime)

displays 08:22.

Today we have timezon +2 but in the winter we have +1. Will this function show correct time during winter and wrong time during summer?

I've tried to set the timezon with

DateTime.SetTimeZone(1)

but it still give me 08:22.

Could someone please explain what happens?

 

[DonManfred]

Dim totaltime As Long = 26520000
DateTime.TimeFormat = "HH:mm:ss"
Log(DateTime.Time(totaltime))
DateTime.SetTimeZone(0)
Log(DateTime.Time(totaltime))

Gives 7:22... I think there must be something magic with the timezone we dont think off actually...
Maybe something about

DateTime.GetTimeZoneOffsetAt(date)

which gets the bias from that day...

 

[Roger C]

Urgh...

Tried

Dim timez As Int
    timez =    DateTime.GetTimeZoneOffsetAt(DateTime.now) ' - 1
    DateTime.SetTimeZone(timez)

First I subtracted one from the timezonoffset, then it displayed 06:22.

Then I just read the offset and set the timezone to the offset and it displayed 08:22.

Then I added 1 to the zon and it displays 10:22


Ok, Now I subtract the offset with 0.5 and I get the correct value of 07:22!!!

timez loaded in SetTimeZon was now 0.5 (Offset always returned 1)

How can the timezon be half? And what will happen when the summertime ends?

Why does the timezon affect my calculation of just hours and minutes? I don't uses days, months or year???

 

[DonManfred]

You said you have tried

DateTime.SetTimeZone(1)

and it gives you 8:22.... I think you should use

DateTime.SetTimeZone(1+DateTime.GetTimeZoneOffsetAt(DateTime.now))

and i think you should use the date from

crnames.GetString("indatum")

instead of Now. GetTimeZoneOffsetAt is the bias i think and it changes when daylightsavingtime changes... so, in combination with the timezone you should get the right value to show the right time

 

[Roger C]

Haha, nope...

Used

DateTime.SetTimeZone(1+DateTime.GetTimeZoneOffsetAt(DateTime.DateParse(crnames.GetString("indatum"))))

since the date is saved as a string like "2014-05-07".
And for my example it shouldn't really matter since I'm using three different timeperiods from today.

I placed it inside the loop in the first post so it could adjust to every date if we are looping through dates before and after the summertime change.

But this returned 14:22 instead, which means it adds an extra hour every time it loops...

Instead of adding 1 I subtracted 0.5 in my post above and then it shows up correctly but I've never heard of half timezones so even if it displays 07:22 now, what will happen in the winter?


Maybe I should have saved ticks in the database instead but then I will have problems in the webinterface and the software in the PC...

 

[DonManfred]

what will happen in the winter?

You´ll see it on 26th october

Maybe I should have saved ticks in the database instead but then I will have problems in the webinterface and the software in the PC...

Yes... i hate such "problems" with date calculations too

Sorry, but more i´m not able to help :-/

 

[Erel]

1. Don't change the timezone.
2. Use DateUtils.PeriodBetween.
3. Don't use DateTime.Time to convert a time period to string. It doesn't work. You should instead create the string yourself from the Period object.

 

[Roger C]

Thank a lot Manfred, You gave it a try, I really appreciate it!

Erel, got you on 1 and 2... but 3... ?
If there's a function for converting ticks to time, why doesn't it work?

But I don't have a Period object!
I use

DateUtils.AddPeriod(totaltime, diff)

This adds a Period to Ticks and returns Ticks...

So How do I convert Ticks to a standard 00:00? That is my question.

I need to get this working on monday and if the libraries can't handle conversions I have to write my own code; take the time and dates directly from the strings with stringfunctions.mid and add them.
Or add the individuals in Period then calculate for days and minutes above 60...

'Loop through these lines with all timeperiods
diff = DateUtils.PeriodBetween(A, B)
thours = thours + diff.Hours
tmins = tmins + diff.Minutes
'End Loop

'Corrections
If diff.Days > 0 Then diff.Hours = diff.Hours + (24 * diff.Days)
' And also the minutes....
'Then display it
lblTime.Text = "Total time: " & thours & ":" & tmins

 

[Erel]

The libraries can handle everything related to date and time. You should however understand the concepts behind them.

The code you posted above is wrong.

What exactly are you trying to implement?

 

[Roger C]

Hi Erel.

I have a database with posts of workorders. Each post contains StartDate, StartTime, EndDate and EndTime.
The dates are usually the same or the day before because users will register worked hours on projects.

Now I want to sort out from the database all posts from a specific date or week or month or get all posts. This is no problem and I show every entry in a ScrollView... but then I want to add together all registered time and display the sum at the end of the list.

So I want to take EndDate/EndTime minus StartDate/StartTime from every single post displayed and get the spent time in that post. Then add them all toghether and show them like 00:00.
Of course it could be 134:22 also but I don't want days to show (like 5 days, 14 hours and 22 minutes). All time are counted in hours and minutes in the projects.

Converting to Ticks and then add the Ticks together worked fine in my first post #1 here, but they didn't display correct. That's where my problem started...

This is the code I have used. Please note that I used the code on line 9 OR on line 12-13, not at the same time.

Added:
The last line works in the emulator, but shows one hour to much in the phone.
(Emulator 07:22, Phone 08:22)
I also realized that I need to show more hours than 24, it could be 134:22 also...

... Now the emulator also shows wrong time (See attached pic, should be 7:22) and I have removed all code for TimeZones...
It worked fine when the emulators TimeZone was set to 0 GMT (which is wrong for Sweden), when I changed it in the emulator to +2 GMT as we have now it started calculating very wrong...

Dim crnames As Cursor
crnames = Main.SQLuser.ExecQuery("SELECT intid, uttid, indatum, utdatum, projekt, kund, projektdescr, week, kommentar FROM time WHERE indatum = '" & lDate & "' ORDER BY intid")

If crnames.RowCount > 0 Then
    For i = 0 To crnames.RowCount - 1
        crnames.Position = i
        If crnames.GetString("indatum")=lDate Then
            'My first try, adding like this works
                totaltime = totaltime + DateTime.DateTimeParse(crnames.GetString("utdatum"), crnames.GetString("uttid"))-DateTime.DateTimeParse(crnames.GetString("indatum"), crnames.GetString("intid"))
            'OR
            'My second try and this works also
                diff = DateUtils.PeriodBetween(DateTime.DateTimeParse(crnames.GetString("indatum"), crnames.GetString("intid")),DateTime.DateTimeParse(crnames.GetString("utdatum"), crnames.GetString("uttid")))
                totaltime = DateUtils.AddPeriod(totaltime, diff)
        End If
    Next
    ' So far everything works, totaltime contains the correct number of ticks
    ' after both ways above

    ' When doing this it all fails, so how should I convert Ticks to "00:00"
    txtTime.Text = "Total tid: " & DateTime.Time(totaltime)
End If

 

[Erel]

If you want to work with hours and seconds then you do not need to use DateUtils.PeriodBetween. Use the "first try" code.

However you cannot use DateTime.Time to convert it to string because it doesn't represent a specific time.

Instead use this code:

Sub ConvertTicksToTimeString(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 "hours, " _
        & NumberFormat(minutes, 2, 0) & " minutes and " & NumberFormat(seconds, 2, 0) & " seconds"
End Sub

It is based on this post: http://www.b4x.com/android/forum/threads/calculations-on-time.21291/#post-123077

 

[Roger C]

Ahh.. ok.
I'll give it a try.

Thanks a lot!!!!

 

[Roger C]

Works like a charm, great!!!

Thank you Erel, what would we do without you?