The "unixepoch" time used by SQLite is an "integer" in whole seconds of precision. ISO-8601 datetime strings are also "by default" generated in seconds of precision. If you use strftime rather than datetime then the ISO8601 strings can be read with "unlimited" precision and written with millisecond precison.
The Julianday formats are IEEE 754 double precision floating point. The epsilon of the current julianday number is 4.65661287307739e-10 days, or about 4.02331352233887e-05 seconds (about 40 microseconds) absolute maximum precision. My "observed" precision on Windows 10 of the julianday number is about a tick. If you used a IEEE 754 double precision floating point to represent the "unixepoch" time, the current epsilon is 2.38418579101563e-07 seconds (about 0.2 microseconds) absolute maximum precision. If you generate and interpret your "timestamps" externally to SQLite3 (ie, in your application and use SQLite3 only for data storage) then you can store and manipulate time values up to the precision limit of your hardware and software. Unixepoch stamps are integers in SQLite3 and therefore have a precision of 1 second. Computations based on the juliandate appear to be limited to about a tick in precision (16.03 ms on my hardware). If you want SQLite3 to generate "floating point unixepoch" you need to use the following expression: (julianday() - 2440587.5) * 86400.0 ie, select (julianday() - 2440587.5) * 86400.0; -- upper limit of precision is about 100 microseconds The built-in strftime function can deal with floating point unixepoch timestamps as long as you tell them they are offset from the unixepoch. select strftime('%Y-%m-%d %H:%S:%f', (julianday() - 2440587.5) * 86400.0, 'unixepoch'), strftime('%Y-%m-%s %H:%M:%f'); --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of danap >Sent: Sunday, 1 July, 2018 12:38 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] Time Precision > >The time precision treated with and defined, ISO-8601, seems to be >with regard to seconds. Storage of an Integer for time as an example >in SQLite: > >sqlite> SELECT STRFTIME('%s', 'now', 'localtime'); >1530446557 > >A 10 digit value. The issue I'm having is with regard to storage >of time, in milliseconds, a 13 digit value. I would assume a more >appropriate precision for a scientific community. > >Looking briefly at the c library for strftime() it does not seem >to provide the possibility for a millisecond precision? > >STRFTIME('%ms'), Integer >or >TIME(DDDDDDDDDDDDD), 13 digits, Text. > >danap. >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users