Time Precision

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
dmp
Reply | Threaded
Open this post in threaded view
|

Time Precision

dmp
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Time Precision

Igor Tandetnik-2
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Time Precision

Keith Medcalf
In reply to this post by dmp

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



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Time Precision

Keith Medcalf
In reply to this post by dmp

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



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Time Precision

David Burgess-2
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
dmp
Reply | Threaded
Open this post in threaded view
|

Re: Time Precision

dmp
In reply to this post by dmp
> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Time Precision

Keith Medcalf

>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-
>[hidden email]] On Behalf Of dmp
>Sent: Monday, 2 July, 2018 11:07
>To: [hidden email]
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
dmp
Reply | Threaded
Open this post in threaded view
|

Re: Time Precision

dmp
In reply to this post by dmp
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users