You can make a user-defined function on Windows that returns the UnixTime to
the limit of Accuracy of the underlying hardware/software (100 huns max) and to
the limit of precision of the IEEE754 double precision floating point format
with the following (so an accuracy of 100 nanoseconds with a precision
(currently) of 0.2 microseconds to the system clock. My system clock has a
dispersion of 36 milliseconds from the actual UTC maintained by the worlds
atomic clocks so the utility of such accuracy and precision is of debatable
value):
SQLITE_PRIVATE void _UnixTime(sqlite3_context *context, int argc, sqlite3_value
**argv)
{
LONGDOUBLE_TYPE timestamp;
sqlite_int64 ftime;
GetSystemTimeAsFileTime((void*)&ftime);
timestamp = (LONGDOUBLE_TYPE)ftime;
sqlite3_result_double(context, (double)((timestamp / 1.0e7) -
11644473600.0));
}
where LONGDOUBLE_TYPE is defined as whatever gives you 128-bit floats on your
compiler, and GetSystemTimeAsFileTime is the windows API that gets the current
time in hundreds of nanoseconds since the ANSI Epoch into a 64-bit integer.
I show variances of +/- 1 millisecond between calculations based off the
julianday() function calculation and the value returned by the above unixtime
function. Of course, julianday is SQL Statement stable and unixtime is
volatile. Although on Windows all the "time" references are based off the same
GetSystemTimeAsFileTime API, maintaining precision is a mathematical concept
and based on the number of calculations (and their order and precision)
performed on the returned value.
---
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
