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-
>[email protected]] On Behalf Of danap
>Sent: Sunday, 1 July, 2018 12:38
>To: [email protected]
>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
>[email protected]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
