SQLite Forum

Bug ? Same query that works in shell produces constraint violation when run by a C program with sqlite3_{prepare,bind,step}
SQLite stores floating point numbers using IEEE-754 64-bit floats.  These
have a precision limit of about 15.95 digits, of which SQLite promises to
preserve 15 digits.  But to store microseconds since 1970, you need 16 digits
of accuracy.

Two approaches jump to mind:

  1.   Store your timestamps as an integer number of microseconds since 1970.
       This is probably the easiest approach.

  2.   Use the [decimal extension][1] to store unlimited precision floating
       point numbers as text.

See further discussion in the [Floating Point Numbers][2] documentation.

[1]: https://www.sqlite.org/floatingpoint.html#the_decimal_c_extension
[2]: https://www.sqlite.org/floatingpoint.html