0

I need to get the difference between two timestamps in SQLite. While searching, I found this question, and while the accepted answer seems to work at first glance, it looses a lot of precision:

Taking this query

select timestamp, EndTimeStamp, ((julianday(EndTimeStamp)-julianday(timestamp)) * 86400.0) as Duration from X

I get this result:

"2016-04-18 13:44:31.630916"    "2016-04-18 13:44:31.63114" "0.00100582838058472"
"2016-04-18 13:44:31.6315231"   "2016-04-18 13:44:31.6316997"   "0.0"
"2016-04-18 13:44:31.6320359"   "2016-04-18 13:44:31.632202"    "0.0"
"2016-04-18 13:44:31.6326964"   "2016-04-18 13:44:31.6329361"   "0.0"

As you can see, the three last results have a calculated difference of Zero, but the timestamps are actually different.

Is it possible, in SQL, with the DATETIME field type, to calculate a precise difference? I would prefer to not need to fallback to C#.

Community
  • 1
  • 1
Lukas Rieger
  • 676
  • 10
  • 31

1 Answers1

1

As documented, the format interpreted by the built-in date/time functions is:

YYYY-MM-DD HH:MM:SS.SSS

In otherwords, they parse only the first three digits of the fractional seconds.

You could try to extract the additional digits with substr() and add them to the result with the correct scale.

CL.
  • 173,858
  • 17
  • 217
  • 259
  • I just now realised that SQLite saves the date as a string, not as a dedicated DATETIME type, so now the behaviour kind of makes sense. Thank you. – Lukas Rieger Apr 18 '16 at 13:16