I am trying to find the difference between two time stamps with milliseconds in SQLITE as below
select abs(CAST ( (JulianDay(t1.TIME1) - JulianDay(t1.TIME2) ) * 24 * 60 * 60 * 1000 AS INTEGER) ) diff_millisec from time_table; For this data:
t1.TIME1 - 14:14:04.446258 t1.TIME2 - 14:14:04.446377 The answer is 0.
For another set of values
t1.TIME1 - 14:13:27.742646 t1.TIME2 - 14:13:27.649184 The answer is 93
Why does the first set gives 0 ? Is there a way to give precise value.
SQLite provides accuracy up to milliseconds when a function like julianday() is used, because only the first three digits (after the dot) are significant to the result, as it is mentioned in Date And Time Functions, so both time strings 14:14:04.446258 and 14:14:04.446377 are evaluated as 14:14:04.446 and are considered equal.
So, the results that you get in milliseconds are correct.
If you want the difference in microseconds, you should add the difference of the last 3 digits of the 2 times:
select abs(CAST( (JulianDay(TIME1) - JulianDay(TIME2)) * 24 * 60 * 60 * 1000 * 1000 + (substr(TIME1, -3) - substr(TIME2, -3)) AS INTEGER )) diff_microsec from time_table; but for better accuracy you should use strftime() to get the difference in seconds and then add the difference in microseconds:
select abs((strftime('%s', TIME1) - strftime('%s', TIME2)) * 1000 * 1000 + (substr(TIME1, -6) - substr(TIME2, -6)) ) diff_microsec from time_table; or:
select abs((strftime('%s', TIME1) - strftime('%s', TIME2)) * 1000 * 1000 + (substr(TIME1, instr(TIME1, '.') + 1) - substr(TIME2, instr(TIME1, '.') + 1)) ) diff_microsec from time_table; See the demo.
