Quiz #3: Time between two dates

Erel

Administrator
Staff member
Licensed User
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
Licensed User
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
 
Upvote 0

Mahares

Expert
Licensed User
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
 
Upvote 0

sorex

Expert
Licensed User
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
 
Upvote 0

Roger Garstang

Well-Known Member
Licensed User
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:
Upvote 0

sorex

Expert
Licensed User
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?
 
Upvote 0

sorex

Expert
Licensed User
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
 
Upvote 0

sorex

Expert
Licensed User
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
 
Upvote 0

Roger Garstang

Well-Known Member
Licensed User
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
 
Upvote 0

COBRASoft

Active Member
Licensed User
Go the 'oldschool' way... Use a while loop and begin from the start date/time until the end :D.
 
Last edited:
Upvote 0

kanaida

Active Member
Licensed User
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.
.DateAdd()
.DatePart()
.DateDiff()
.Subtract()
.Add()
.Week()
.Month()
.Year()
.DayOfWeek()

etc...
 
Upvote 0

Erel

Administrator
Staff member
Licensed User
@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
   Return total
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
   Return total
End Sub
 
Upvote 0

sorex

Expert
Licensed User
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 ?
 
Upvote 0

Erel

Administrator
Staff member
Licensed User
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
 
Upvote 0

Roger Garstang

Well-Known Member
Licensed User
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.
 
Upvote 0

kanaida

Active Member
Licensed User
Thanks erel, somehow I missed the date/time functions the first time around. This was many versions back though.
 
Upvote 0
Top