Re: [sqlite] Time Precision
Keith Medcalf wrote: > 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) The time precision issue revolved around the lack of date, time, etc. types in SQLite and my Java application GUI using a JDBC. The java.sql.Time class is defined as: Time(long time) Constructs a Time object using a milliseconds time value. A JDBC setTime() or getTime() expects this long integer. Most databases I would assume stores a time type as a long intger with precision of milliseconds even though the ISO-8601 standard defines precsion to seconds. The conversions using the datetime functions with SQLite as I was using them did not give me milliseconds precsion. With a greater understanding of those functions I was able to create the precision needed. Seems it was one of the of the recommended solutions provided by you that solved my issues. danap. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Time Precision
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 8640 */
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();
*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(D), 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 syst
Re: [sqlite] Time Precision
danap wrote:
> SELECT CAST((SELECT (julianday('now', 'localtime') -
> julianday('1970-01-01'))*24*60*60*1000) AS INTEGER);
Keith wrote:
> Are you sure you want to be mixing up timezones?
>
> julianday('1970-01-01') returns the julianday timestamp
> for 1970-01-01 00:00:00 GMT julianday('now', 'localtime')
> returns the julianday timestamp for 'now' in your
> current timezone.
No. You are correct and after my initial posting reply, above, fixed
the Time Precision to your initial suggested example, 'now', only in
the code.
danap.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Time Precision
>SELECT CAST((SELECT (julianday('now', 'localtime') -
>julianday('1970-01-01'))*24*60*60*1000) AS INTEGER);
Are you sure you want to be mixing up timezones?
julianday('1970-01-01') returns the julianday timestamp for 1970-01-01 00:00:00
GMT
julianday('now', 'localtime') returns the julianday timestamp for 'now' in your
current timezone.
The stored result will be the offset in milliseconds from the "wall clock time"
at Greenwich England, to the "wall clock time" at your current location
(excluding travel time by car/train/steamer/airplane). It will have folds
based on the timezone (that is, it will not be monotonic).
---
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 dmp
>Sent: Monday, 2 July, 2018 11:07
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] Time Precision
>
>> Igor wrote:
>> select (julianday('now') - julianday('1970-01-01'))*24*60*60*1000
>
>> Keith wrote:
>> select (julianday() - 2440587.5) * 86400.0
>
>Both of these got me on my way, Igor's a little more clearer. I'll
>doing a little more checking to insure the solution below is correct,
>but seems good. Thanks.
>
>danap.
>
>Solution:
>
>SELECT CAST(
>(SELECT (julianday('now', 'localtime') -
>julianday('1970-01-01'))*24*60*60*1000)
>AS INTEGER);
>
>___
>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
Re: [sqlite] Time Precision
> Igor wrote:
> select (julianday('now') - julianday('1970-01-01'))*24*60*60*1000
> Keith wrote:
> select (julianday() - 2440587.5) * 86400.0
Both of these got me on my way, Igor's a little more clearer. I'll
doing a little more checking to insure the solution below is correct,
but seems good. Thanks.
danap.
Solution:
SELECT CAST(
(SELECT (julianday('now', 'localtime') -
julianday('1970-01-01'))*24*60*60*1000)
AS INTEGER);
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Time Precision
Too long since I have coded for windows. BUT getting a accurate time/interval from a loaded windows system is non-trivial. The multimedia timers are ok (from memory). ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Time Precision
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*));
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(D), 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
Re: [sqlite] Time Precision
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(D), 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
Re: [sqlite] Time Precision
On 7/1/2018 2:37 PM, danap wrote:
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.
select (julianday('now') - julianday('1970-01-01'))*24*60*60*1000
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[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(D), 13 digits, Text.
danap.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
