# Quiz #3: Time between two dates

#### Erel

Staff member
The only correct solution is to use DateUtils: DateUtils - Simplifies Date and Time Calcuations

Here is a simple one (or not...):

Write a sub with the following signature:
B4X:
``````Sub HoursBetween(StartDate As String, StartTime As String, _
EndDate As String, EndTime As String) As Int``````

This sub should calculate the hours between the two dates.
Each date represents a meeting that the user has added in the calendar.
Assume that the strings are written correctly and the default formats are used.

Examples:
B4X:
``````Log(HoursBetween("1/17/2012", "16:30:00", "1/17/2013", "16:30:00")) '8784 => 366 * 24
Log(HoursBetween("1/17/2013", "16:30:00", "1/17/2014", "16:30:00")) '8760 => 365 * 24
Log(HoursBetween("1/17/2013", "16:30:00", "8/17/2013", "16:30:00")) '???``````

There might be several approaches to this problem. The way I solved it is quite complicated. :sign0013:

Last edited:

#### galimpic

##### Member
B4X:
``````Sub HoursBetween(StartDate As String, StartTime As String, _
EndDate As String, EndTime As String) As Int
Dim ed As Long = DateTime.DateParse(EndDate)
Dim sd As Long = DateTime.DateParse(StartDate)
Dim et As Long = DateTime.TimeParse(EndTime)
Dim st As Long = DateTime.TimeParse(StartTime)
Dim ticks As Long = ed - sd + et - st
Dim hours As Int = ticks / 3600000
Return hours
End Sub``````

HoursBetween("1/17/2013", "16:30:00", "8/17/2013", "16:30:00") = 5087

#### Mahares

##### Expert
Here it is Professor Erel. I hope I get your blessing:
B4X:
``````Dim SQL1 as SQL
Dim txt, OrigDateFormat, OrigTimeFormat  As String``````

B4X:
``````OrigDateFormat=DateTime.DateFormat
OrigTimeFormat=DateTime.Timeformat
HoursBetween("1/17/2013", "16:30:00" , "8/17/2013", "16:30:00")   'returns 5088 hours``````
B4X:
``````Sub HoursBetween(StartDate As String, StartTime As String, EndDate As String, EndTime As String) As Int
Dim MyHours As Int
Dim ED,ET,SD,ST As Long
SD=DateTime.DateParse(StartDate) :ST=DateTime.TimeParse(StartTime)
ED=DateTime.DateParse(EndDate) :ET=DateTime.TimeParse(EndTime)
DateTime.DateFormat = "yyyy-MM-dd"
DateTime.TimeFormat = "HH:mm:ss"
StartDate=DateTime.Date(SD) :StartTime=DateTime.Time(ST)
EndDate=DateTime.Date(ED) :EndTime=DateTime.Time(ET)

SQL1.Initialize("","",False) 'Create and initialize a dummy database
txt= "SELECT julianday('" & EndDate & " " & EndTime & "') - julianday('" & StartDate & " " & StartTime & "')"
MyHours= SQL1.ExecQuerySingleResult(txt) *24
Msgbox(MyHours,"")     'returns 5088
DateTime.DateFormat=OrigDateFormat  'return format to original
DateTime.Timeformat=OrigTimeFormat
End Sub``````

#### sorex

##### Expert
this one workss with 2 parameters

result also shows 5087

but since I'm still a B4A n00b there might be better options

B4X:
``````msgbox(HoursBetween("1/17/2013 16:30:00","8/17/2013 16:30:00"),"")

Sub HoursBetween(StartDate As String,EndDate As String) As Int
startticks=DateTime.DateParse(StartDate.SubString2(0,StartDate.indexOf(" "))) + DateTime.timeparse(StartDate.substring2(StartDate.indexOf(" ")+1,StartDate.Length))
endticks=DateTime.DateParse(EndDate.SubString2(0,EndDate.indexOf(" ")))+DateTime.timeparse(EndDate.substring2(EndDate.indexOf(" ")+1,EndDate.Length))
Return (endticks-startticks)/DateTime.TicksPerHour
End Sub``````

#### Roger Garstang

##### Well-Known Member
this one workss with 2 parameters

result also shows 5087

but since I'm still a B4A n00b there might be better options

If you set your DateFormat to a string like I gave in the example then just parse it there is no need for all the substring stuff (Your format is different though, so you'd need to switch my example text to match). B4A calling them DateFormat and TimeFormat is also confusing...think of them as Format1 and Format2. They can both contain either or both Date and Time. Just call the parse for whichever format you need.

Last edited:

#### sorex

##### Expert
here's the 4 param one

B4X:
``````Sub HoursBetween(StartDate As String,StartTime As String,Enddate As String, EndTime As String) As Int
startticks=DateTime.DateParse(StartDate)+DateTime.timeparse(StartTime)
endticks=DateTime.DateParse(Enddate)+DateTime.timeparse(EndTime)
Return (endticks-startticks)/DateTime.TicksPerHour
End Sub``````

Roger: you sure about that? I thought it always defaulted to 00:00 if you give it the full string?

#### sorex

##### Expert
this returns 24

B4X:
``````Msgbox(HoursBetween("8/16/2013 16:30:00","8/17/2013 17:30:00"),"")

Sub HoursBetween(StartDate As String,Enddate As String) As Int
Return (DateTime.DateParse(Enddate)-DateTime.DateParse(StartDate))/DateTime.TicksPerHour
End Sub``````

this one returns 25 which is correct

B4X:
``````Msgbox(HoursBetween2param("8/16/2013 16:30:00","8/17/2013 17:30:00"),"")

Sub HoursBetween2param(StartDate As String,EndDate As String) As Int
startticks=DateTime.DateParse(StartDate.SubString2(0,StartDate.indexOf(" "))) + DateTime.timeparse(StartDate.substring2(StartDate.indexOf(" ")+1,StartDate.Length))
endticks=DateTime.DateParse(EndDate.SubString2(0,EndDate.indexOf(" ")))+DateTime.timeparse(EndDate.substring2(EndDate.indexOf(" ")+1,EndDate.Length))
Return (endticks-startticks)/DateTime.TicksPerHour
End Sub``````

#### Roger Garstang

##### Well-Known Member
In your first example you have to set the DateFormat to match what you are passing, otherwise it uses the default which is only date and no time...so it ignores your time and compares the 2 dates which are 24hrs apart.

#### sorex

##### Expert
Tried that but now the only thing I get is a "Unparsable date" error although I feed it with "2013-08-16 16:30" or "2013-8-16 16:30" and use datatime.format="yyyy-MM-dd HH:mm" before the sub call

#### Roger Garstang

##### Well-Known Member
Not sure what is missing in your code, but this works fine:

B4X:
``````'Activity module
Sub Process_Globals

End Sub

Sub Globals

End Sub

Sub Activity_Create(FirstTime As Boolean)

End Sub

Sub Activity_Resume
Msgbox(HoursBetween("8/16/2013 16:30:00", "8/17/2013 17:30:00"),"")
End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub

Sub HoursBetween(StartDate As String, Enddate As String) As Int
DateTime.DateFormat= "MM/dd/yyyy HH:mm:ss"
Return (DateTime.DateParse(Enddate)-DateTime.DateParse(StartDate))/DateTime.TicksPerHour
End Sub``````

#### sorex

##### Expert
my bad, I was calling the wrong sub

works fine, thanks for pointing that out!

#### COBRASoft

##### Active Member
Go the 'oldschool' way... Use a while loop and begin from the start date/time until the end .

Last edited:

#### kanaida

##### Active Member
wish we had a built in class to do the equivalent of the DateTime class and all of it's functions that we get in vb. It was pretty bizzare at first for me too.
.DatePart()
.DateDiff()
.Subtract()
.Week()
.Month()
.Year()
.DayOfWeek()

etc...

#### Erel

Staff member
@kanaida, most of these methods are already supported. The ones missing can be easily implemented in your code.

Here are the results...
Roger's code is the only one that works correctly in all cases. :icon_clap:
The problematic day is the day when the DST changes. On the device I tested it (Jerusalem time zone) it happens on March 29 2013.

I will start with my code. The ParseDateAndTime method is very useful. It might be added to the core library in the future. In the meantime you can use it in your projects. Once you correctly combine the two parts (date and time) the second step is simple.
B4X:
``````Sub HoursBetween(StartDate As String, StartTime As String, _
EndDate As String, EndTime As String) As Int
Dim s, e As Long
s = ParseDateAndTime(StartDate, StartTime)
e = ParseDateAndTime(EndDate, EndTime)
Return (e - s) / DateTime.TicksPerHour
End Sub

'Returns the ticks value of the given date and time (based on the device time zone).
Sub ParseDateAndTime(d As String, t As String) As Long
Dim dd = DateTime.DateParse(d), tt = DateTime.TimeParse(t) As Long
tt = (tt + DateTime.TimeZoneOffset * DateTime.TicksPerHour) Mod DateTime.TicksPerDay
Dim total As Long
total = dd + tt + _
(DateTime.GetTimeZoneOffsetAt(dd) - DateTime.GetTimeZoneOffsetAt(dd + tt)) _
* DateTime.TicksPerHour
End Sub``````

Here is the code I used to test the different suggestions:
B4X:
``````Sub Process_Globals
Type Test(d1 As String, t1 As String, d2 As String, t2 As String, result As Int)
Dim SQL1 As SQL
Dim txt, OrigDateFormat, OrigTimeFormat  As String
End Sub

Sub Globals
'These global variables will be redeclared each time the activity is created.
'These variables can only be accessed from this module.

End Sub

Sub CreateTest(d1 As String, t1 As String, d2 As String, t2 As String) As Test
Dim t As Test
t.Initialize
t.d1 = d1
t.d2 = d2
t.t1 = t1
t.t2 = t2
Return t
End Sub

Sub Activity_Create(FirstTime As Boolean)
Dim tests() As Test = Array As Test( _
CreateTest("1/17/2012", "16:30:00", "1/17/2013", "16:30:00"), _
CreateTest("1/17/2013", "16:30:00", "1/17/2014", "16:30:00"), _
CreateTest("03/28/2013", "05:00:00", "03/29/2013", "05:00:00"))
'Calculate correct results
For Each t As Test In tests
t.result = HoursBetween(t.d1, t.t1, t.d2, t.t2)
Log(t.result)
Next
Log("Galimpic")
For Each t As Test In tests
Dim r As Int = GalimpicHoursBetween(t.d1, t.t1, t.d2, t.t2)
If r <> t.result Then Log("ERROR!!!!!! " & r & " " & t)
Next

Log("Mahares")
For Each t As Test In tests
Dim r As Int = MaharesHoursBetween(t.d1, t.t1, t.d2, t.t2)
If r <> t.result Then Log("ERROR!!!!!! " & r & " " & t)
Next

Log("Sorex")
For Each t As Test In tests
Dim r As Int = SorexHoursBetween(t.d1, t.t1, t.d2, t.t2)
If r <> t.result Then Log("ERROR!!!!!! " & r & " " & t)
Next

Log("Roger") 'GOOD JOB!!!
For Each t As Test In tests
Dim r As Int = RogerHoursBetween(t.d1 & " " & t.t1, t.d2 & " " & t.t2)
If r <> t.result Then Log("ERROR!!!!!! " & r & " " & t)
Next

End Sub

Sub SorexHoursBetween(StartDate As String,StartTime As String,Enddate As String, EndTime As String) As Int
startticks=DateTime.DateParse(StartDate)+DateTime.timeparse(StartTime)
endticks=DateTime.DateParse(Enddate)+DateTime.timeparse(EndTime)
Return (endticks-startticks)/DateTime.TicksPerHour
End Sub

Sub GalimpicHoursBetween(StartDate As String, StartTime As String, _
EndDate As String, EndTime As String) As Int
Dim ed As Long = DateTime.DateParse(EndDate)
Dim sd As Long = DateTime.DateParse(StartDate)
Dim et As Long = DateTime.TimeParse(EndTime)
Dim st As Long = DateTime.TimeParse(StartTime)
Dim ticks As Long = ed - sd + et - st
Dim hours As Int = ticks / 3600000
Return hours
End Sub

Sub MaharesHoursBetween(StartDate As String, StartTime As String, EndDate As String, EndTime As String) As Int
OrigDateFormat=DateTime.DateFormat
OrigTimeFormat=DateTime.Timeformat
Dim MyHours As Int
Dim ED,ET,SD,ST As Long
SD=DateTime.DateParse(StartDate) :ST=DateTime.TimeParse(StartTime)
ED=DateTime.DateParse(EndDate) :ET=DateTime.TimeParse(EndTime)
DateTime.DateFormat = "yyyy-MM-dd"
DateTime.TimeFormat = "HH:mm:ss"
StartDate=DateTime.Date(SD) :StartTime=DateTime.Time(ST)
EndDate=DateTime.Date(ED) :EndTime=DateTime.Time(ET)

SQL1.Initialize("","",False) 'Create and initialize a dummy database
txt= "SELECT julianday('" & EndDate & " " & EndTime & "') - julianday('" & StartDate & " " & StartTime & "')"
MyHours= SQL1.ExecQuerySingleResult(txt) *24
DateTime.DateFormat=OrigDateFormat  'return format to original
DateTime.Timeformat=OrigTimeFormat
Return MyHours
End Sub

Sub RogerHoursBetween(StartDate As String, Enddate As String) As Int
DateTime.DateFormat= "MM/dd/yyyy HH:mm:ss"
Return (DateTime.DateParse(Enddate)-DateTime.DateParse(StartDate))/DateTime.TicksPerHour
End Sub

Sub HoursBetween(StartDate As String, StartTime As String, _
EndDate As String, EndTime As String) As Int
Dim s, e As Long
s = ParseDateAndTime(StartDate, StartTime)
e = ParseDateAndTime(EndDate, EndTime)
Return (e - s) / DateTime.TicksPerHour
End Sub

Sub ParseDateAndTime(d As String, t As String) As Long
Dim dd = DateTime.DateParse(d), tt = DateTime.TimeParse(t) As Long
tt = (tt + DateTime.TimeZoneOffset * DateTime.TicksPerHour) Mod DateTime.TicksPerDay
Dim total As Long
total = dd + tt + _
(DateTime.GetTimeZoneOffsetAt(dd) - DateTime.GetTimeZoneOffsetAt(dd + tt)) _
* DateTime.TicksPerHour
End Sub``````

#### sorex

##### Expert
Just wondering...

Why would my or the other people's code export a wrong result on that DST thing?

The date+time parse should equal the same ticks as Roger's straight substraction.

So this is a bug in java ?

#### Erel

Staff member
Good question.

This code returns a different result than what I believe you are expecting:
B4X:
``startticks=DateTime.DateParse(StartDate)+DateTime.timeparse(StartTime)``
The value returned is more or less not meaningful. Try this code:
B4X:
``Log(DateTime.Date(startticks)) 'will return something like year 4000.``
The reason is that the DateTime.TimeParse method assumes that the date part is today. So DateTime.TimeParse("16:30:00") refers to today at 16:30.

Still in this specific case the results you get are almost always correct. The reason is that the "extra today value" is added to both sides.

It is important to understand that it is incorrect to add the two values. You should instead use the ParseDateAndTime sub that I wrote.

This line removes the extra value from the time part:
B4X:
``tt = (tt + DateTime.TimeZoneOffset * DateTime.TicksPerHour) Mod DateTime.TicksPerDay``

What happens on the DST change day? The change happens at 01:00 or 02:00 am.
As DateTime.DateParse returns the date value at 00:00, the change is not applied on this specific day. The following code fixes this:
B4X:
``````total = dd + tt + _
(DateTime.GetTimeZoneOffsetAt(dd) - DateTime.GetTimeZoneOffsetAt(dd + tt)) _
* DateTime.TicksPerHour``````

#### Roger Garstang

##### Well-Known Member
Just wondering...

Why would my or the other people's code export a wrong result on that DST thing?

The date+time parse should equal the same ticks as Roger's straight substraction.

So this is a bug in java ?

The other thing to consider is when you split off the time from the date you lose the link that determines the DST shift or both the date portion and the time portion will shift and give incorrect results too.

The other things that make a mess are file systems. FAT/FAT32 usually store the local date/time statically and being in another timezone or DST shift still shows the original timezone's time. NTFS and some other filesystems fixed this by storing GMT time. The catch after this though is how they handle DST. Windows will shift it by the current date's DST shift instead of the shift at the time the file was created. This makes your files lose or gain an hour throughout the year.

When I worked with old DSD Route handhelds they checked the timestamp on downloads to determine if they got it already. I had to write an app to remotely connect to every Windows machine at midnight and shift thousands of files plus or minus an hour opposite what DST would shift at 2am so when they connected it would see the correct date.

#### kanaida

##### Active Member
Thanks erel, somehow I missed the date/time functions the first time around. This was many versions back though.

Replies
1
Views
225
Replies
71
Views
39K
Replies
34
Views
43K
Replies
21
Views
5K
Replies
106
Views
88K