I has some fun letting SQLite calculate it.
[code]Var inMemoryDB As New SQLiteDatabase
Var vSQL As String
// shift first part 09:05:38 -> 13:23:47 or 4:18:09
// lunch from 13:13:47 -> 13:54:02 or 0:41:45
// shift second part 13:54:02 -> 17:12:58 or 03:18:56
Var vShift_Start As String = “09:05:38”
Var vLunch_Start As String = “13:23:47”
Var vLunch_End As String = “13:54:02.5” // Even added down to the 10th of a second
Var vShift_End As String = “17:12:58.2” // Even added down to the 10th of a second
Var vFirstShiftTimeDouble As Double
Var vSecondShiftTimeDouble As Double
Var vTotalShiftTimeDouble As Double
Var vLunchTimeDouble As Double
Var vFirstShift As String
Var vSecondShift As String
Var vTotalShift As String
Var vTotalShiftUpTo_ms As String
Var vLunch As String
Try
inMemoryDB.Connect
Catch error As DatabaseException
inMemoryDB=nil
End Try
If inMemoryDB<>Nil Then
vSQL = “SELECT CAST((julianday(’” + vLunch_Start + “’) - julianday(’” + vShift_Start + “’)) * 24 As double) AS firstshifttimedouble,” + _
“CAST((julianday(’” + vShift_End + “’) - julianday(’” + vLunch_End + “’)) * 24 As double) AS secondshifttimedouble,” + _
“CAST((julianday(’” + vLunch_Start + “’) - julianday(’” + vShift_Start + "’)) * 24 As double) + " + _
“CAST((julianday(’” + vShift_End + “’) - julianday(’” + vLunch_End + "’)) * 24 As double) AS totalshifttimedouble, " + _
“CAST((julianday(’” + vLunch_End + “’) - julianday(’” + vLunch_Start + "’)) * 24 As double) AS lunchtimedouble, " + _
“time(CAST((julianday(’” + vLunch_Start + “’) - julianday(’” + vShift_Start + "’)) * 24 * 60 * 60 As double),‘unixepoch’) AS firstshift, " + _
“time(CAST((julianday(’” + vShift_End + “’) - julianday(’” + vLunch_End + "’)) * 24 * 60 * 60 As double),‘unixepoch’) AS secondshift, " + _
“time(CAST((julianday(’” + vLunch_Start + “’) - julianday(’” + vShift_Start + "’)) * 24 * 60 * 60 As double) + " + _
“CAST((julianday(’” + vShift_End + “’) - julianday(’” + vLunch_End + "’)) * 24 * 60 * 60 As double),‘unixepoch’) AS totalshift, " + _
“strftime(’%H:%M:%f’,CAST((julianday(’” + vLunch_Start + “’) - julianday(’” + vShift_Start + "’)) * 24 * 60 * 60 As double) + " + _
“CAST((julianday(’” + vShift_End + “’) - julianday(’” + vLunch_End + "’)) * 24 * 60 * 60 As double),‘unixepoch’) AS totalshift_up_to_ms, " + _
“time(CAST((julianday(’” + vLunch_End + “’) - julianday(’” + vLunch_Start + “’)) * 24 * 60 * 60 As double),‘unixepoch’) AS lunch;”
Var results As RowSet
Try
results = inMemoryDB.SelectSQL(vSQL)
If results <> Nil and Not results.AfterLastRow Then
//double values
vFirstShiftTimeDouble = results.ColumnAt(0).DoubleValue
vSecondShiftTimeDouble = results.ColumnAt(1).DoubleValue
vTotalShiftTimeDouble = results.ColumnAt(2).DoubleValue
vLunchTimeDouble = results.ColumnAt(3).DoubleValue
//string values
vFirstShift = results.ColumnAt(4).StringValue
vSecondShift = results.ColumnAt(5).StringValue
vTotalShift = results.ColumnAt(6).StringValue
vTotalShiftUpTo_ms = results.ColumnAt(7).StringValue
vLunch = results.ColumnAt(8).StringValue
results.Close
End If
Catch error As DatabaseException
MessageBox("Error: " + error.Message)
End Try
End If
[/code]
