Are you running Windows or Unix? I am sending this to you as I was just
looking into this again and although SQLite maintains time internally with a
millisecond precision, the API used on Windows to read the time is limited by
the Clock Resolution (usually about 16.5 ms). If you are using Windows 8 or
later, then you can edit the SQLite3 amalgamation code (and/or the winfvs
source) and use the GetSystemTimePreciseAsFileTime call rather than the
GetSystemTimeAsFileTime call (around line 40866 in the aamalgamation code)
#if !SQLITE_OS_WINCE
{ "GetSystemTimeAsFileTime", (SYSCALL)GetSystemTimePreciseAsFileTime, 0 },
#else
{ "GetSystemTimeAsFileTime", (SYSCALL)0, 0 },
#endif
The attached extension (which *must* be included inline with the amalgamation
code) adds functions which return datetimes with localization and/or
milliseconds, and a separate unixtime function that returns the unixtime as a
floating point number while still using the VDBE time stability guarantees (it
uses the internal SQLite3 APIs to do most of the work).
Using the sqltime.c functions also requires some minor changes to the
amalgamation code so that the localtime offset is kept when the localtime
modifier is applied:
/*
** A structure for holding a single date and time.
*/
typedef struct DateTime DateTime;
struct DateTime {
sqlite3_int64 iJD; /* The julian day number times 86400000 */
int Y, M, D; /* Year, month, and day */
int h, m; /* Hour and minutes */
int tz; /* Timezone offset in minutes */
double s; /* Seconds */
char validJD; /* True (1) if iJD is valid */
char rawS; /* Raw numeric value stored in s */
char validYMD; /* True (1) if Y,M,D are valid */
char validHMS; /* True (1) if h,m,s are valid */
char validTZ; /* True (1) if tz is valid */
char tzSet; /* Timezone was set explicitly */
char isError; /* An overflow has occurred */
sqlite3_int64 offs; /* Localtime Offset */ /* ********** */
};
The field offs has been added to the end of the structure and in function
localtimeOffset just before the return ...
static sqlite3_int64 localtimeOffset(
DateTime *p, /* Date at which to calculate offset */
sqlite3_context *pCtx, /* Write error here if one occurs */
int *pRc /* OUT: Error code. SQLITE_OK or ERROR */
){
...
computeJD(&y);
*pRc = SQLITE_OK;
p->offs = y.iJD - x.iJD; /* ********** */
return y.iJD - x.iJD;
}
The offset is saved.
---
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
/*
** NB: On Windows, the SQLite3 Time & Date functions, even though the time
** is maintained in milliseconds since the Julian Epoch, is limited by
** the timer resolution, and the DateTime values are limited to this
** precision. Increassing the timer resolution will increase the
** overhead used by the Operating System in maintaining the system
** clock since it is updated much more frequently.
**
** Modifying the SQLite3 winvfs to utilize the
GetSystemTimePreciseAsFileTime
** API instead of the GetSystemTimeAsFileTime API (search this in the code)
** will ensure that all time & date functions use the maximum precision of
** system clock that can be obtained (100 ns in theory) and the
iCurrentTime
** as maintained by the VDBE will always be accurate to the millisecond.
**
** The granularity of the timer and the clock resolution will not be
affected
** by using the GetSystemTimePreciseAsFileTime API nor will the overhead
** of changing the interrupt rate for maintaining the system clock. The
** (small) overhead will instead occur only when the system clock is read.
**
** This file must be compiled as part of the Amalgamation by appending
** the file to the end of the Amalgamation sqlite3.c file
**
** The function UnixTime, DateTimeMS and TimeMS follow the time
** stability guarantees in effect for the SQLite3 engine.
*/
/*
** Set the Windows Timer Resolution to the specified number of Milliseconds
** after clearing any timeBeginPeriod set in this application
*/
static void _TimeResolution(sqlite3_context *context, int argc, sqlite3_value
**argv) {
UINT res;
if (argc == 1) {
if (sqlite3_value_type(argv[0]) != SQLITE_NULL) {
res = sqlite3_value_int64(argv[0]);
if (res > 0) {
for (UINT i=1; i++; i<18) {
timeEndPeriod(i);
}
timeBeginPeriod(res);
}
}
}
}
/*
** UnixTime function returns the floating point unix epoch time.
**
** A JulianDay (floating point time since the Julian Epoch as
** returned by the JulianDay function) may also be provided in
** which case the JulianDay is converted to a UnixTime.
*/
static void _UnixTime(sqlite3_context *context, int argc, sqlite3_value **argv)
{
register double ltime;
if (argc == 0) {
ltime = sqlite3StmtCurrentTime(context); /* Julian Epoch
Milliseconds */
} else if ((argc == 1 && sqlite3_value_type(argv[0]) != SQLITE_NULL)) {
ltime = sqlite3_value_double(argv[0]);
ltime *= 86400000.0; /* Julian Days to
Milliseconds */
} else {
return;
}
ltime -= 210866760000000.0; /* Convert Junlian Epoch to Unix
Epoch */
ltime /= 1000.0; /* Convert Milliseconds to Seconds
*/
sqlite3_result_double(context, (double)ltime);
}
/*
** DateTimeMS is the same as the builtin DateTime function except that it
returns
** a string which includes the milliseconds. If the user_context is non-zero
also
** localize the string by including the localtime offset.
*/
static void _datetimemsFunc(sqlite3_context *context, int argc, sqlite3_value
**argv) {
DateTime x;
if (isDate(context, argc, argv, &x) == 0) {
char zBuf[100];
computeYMD_HMS(&x);
if (sqlite3_user_data(context)) {
int oM = x.offs / 60000;
int oH = oM / 60;
oM = abs(oM % 60);
sqlite3_snprintf(sizeof(zBuf), zBuf, "%04d-%02d-%02d
%02d:%02d:%06.3f %+03d:%02d",
x.Y, x.M, x.D, x.h, x.m, x.s,
oH, oM);
} else {
sqlite3_snprintf(sizeof(zBuf), zBuf, "%04d-%02d-%02d
%02d:%02d:%06.3f",
x.Y, x.M, x.D, x.h, x.m, x.s);
}
sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
}
}
/*
** TimeMS is the same as the builtin Time function except that is returns a
string
** which includes the milliseconds
*/
static void _timemsFunc(sqlite3_context *context, int argc, sqlite3_value
**argv) {
DateTime x;
if (isDate(context, argc, argv, &x) == 0) {
char zBuf[100];
computeHMS(&x);
sqlite3_snprintf(sizeof(zBuf), zBuf, "%02d:%02d:%06.3f", x.h, x.m, x.s);
sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
}
}
/*
** This is the initialization function which must be called to initialize these
functions
** on the current connection.
*/
static int sqlite3_sqltime_init(sqlite3 *db, char **pzErrMsg, const
sqlite3_api_routines *pApi)
{
int nErr = 0;
nErr += sqlite3_create_function(db, "TimeResolution", -1, SQLITE_UTF8,
0, _TimeResolution, 0, 0);
nErr += sqlite3_create_function(db, "UnixTime", -1, SQLITE_UTF8,
0, _UnixTime, 0, 0);
nErr += sqlite3_create_function(db, "DateTimeMS", -1, SQLITE_UTF8,
0, _datetimemsFunc, 0, 0);
nErr += sqlite3_create_function(db, "DateTimeTZ", -1, SQLITE_UTF8,
(void*)1, _datetimemsFunc, 0, 0);
nErr += sqlite3_create_function(db, "TimeMS", -1, SQLITE_UTF8,
0, _timemsFunc, 0, 0);
return nErr ? SQLITE_ERROR : SQLITE_OK;
}
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
