Re: [sqlite] millisecond precision for unixepoch values
Igor Tandetnik wrote:
> You have two problems. First, 1219441430151/1000 is done as C-style
> integer truncating division, so you are losing your fractions right
> there. Try
>
> select 1219441430151/1000, 1219441430151/1000.0;
>
> and see the difference.
>
Ah, indeed:
sqlite> select 1219441430151/1000, 1219441430151/1000.0;
1219441430|1219441430.151
> Second, strftime('%s') is documented as printing the number of seconds.
> Even if its parameter is a fraction, its output is always an integer
> (or, to be precise, a string of digits convertible to an integer).
>
>
> You can convert "seconds since Unix epoch" to Julian day like this:
>
> J = U / 86400.0 + 2440587.5
>
> 86400 is the number of seconds in a day. 2440587.5 is the result of
> julianday('1970-01-01') - the difference between Unix epoch and Julian
> epoch.
>
Brilliant, thanks, that works great.
The presumed inverse |U = (J - 2440587.5) * 86400.0| also mostly works,
except that it doesn't return the original integer, and JavaScript's
Date constructor apparently converts fractional inputs to integers by
flooring (truncating) them, resulting in an off-by-one error for
1219441430151:
select (2454701.40544156 - 2440587.5) * 86400.0;
1219441430.15077
1219441430.15077 * 1000 -> 1219441430150.77 (in both SQLite and JS)
new Date(1219441430150.77).getTime() -> 1219441430150
I suppose I can fix that in most cases by rounding the value in either
SQLite or in JS before passing it to the Date constructor.
-myk
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] millisecond precision for unixepoch values
Igor Tandetnik wrote: > Scott Baker <[EMAIL PROTECTED]> wrote: >> Did I do something wrong? >> >> SQLite version 3.5.9 >> Enter ".help" for instructions >> sqlite> select 1219441430151/1000, 1219441430151/1000.0; >> 1219441430| > > Works for me. Did you perhaps compile without floating point support, or > something like that? I'm running Windows pre-built binaries. I'm using the fedora packages: [EMAIL PROTECTED](~) :rpm -q sqlite sqlite-3.5.9-1.fc9.i386 It works for small numbers: sqlite> select 1.0 / 2.0; 0.5 -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] millisecond precision for unixepoch values
Scott Baker <[EMAIL PROTECTED]> wrote: > Did I do something wrong? > > SQLite version 3.5.9 > Enter ".help" for instructions > sqlite> select 1219441430151/1000, 1219441430151/1000.0; > 1219441430| Works for me. Did you perhaps compile without floating point support, or something like that? I'm running Windows pre-built binaries. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] millisecond precision for unixepoch values
Igor Tandetnik wrote:
> Myk Melez <[EMAIL PROTECTED]> wrote:
>> I noticed today that JavaScript Date values (the number of
>> milliseconds since the Unix epoch) degrade to second precision when
>> converted to Julian date values and back using naive SQLite date/time
>> function calls, for example:
>>
>> sqlite> SELECT strftime('%s', julianday(1219441430151/1000,
>> 'unixepoch')) * 1000;
>> 121944143
>>
>> I suppose this is because "Unix has no tradition of directly
>> representing non-integer Unix time numbers as binary fractions."
>
> No. You have two problems. First, 1219441430151/1000 is done as C-style
> integer truncating division, so you are losing your fractions right
> there. Try
>
> select 1219441430151/1000, 1219441430151/1000.0;
Did I do something wrong?
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> select 1219441430151/1000, 1219441430151/1000.0;
1219441430|
I don't get a result for the second select...
--
Scott Baker - Canby Telcom
RHCE - System Administrator - 503.266.8253
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] millisecond precision for unixepoch values
Myk Melez <[EMAIL PROTECTED]> wrote:
> I noticed today that JavaScript Date values (the number of
> milliseconds since the Unix epoch) degrade to second precision when
> converted to Julian date values and back using naive SQLite date/time
> function calls, for example:
>
> sqlite> SELECT strftime('%s', julianday(1219441430151/1000,
> 'unixepoch')) * 1000;
> 121944143
>
> I suppose this is because "Unix has no tradition of directly
> representing non-integer Unix time numbers as binary fractions."
No. You have two problems. First, 1219441430151/1000 is done as C-style
integer truncating division, so you are losing your fractions right
there. Try
select 1219441430151/1000, 1219441430151/1000.0;
and see the difference.
Second, strftime('%s') is documented as printing the number of seconds.
Even if its parameter is a fraction, its output is always an integer
(or, to be precise, a string of digits convertible to an integer).
You can convert "seconds since Unix epoch" to Julian day like this:
J = U / 86400.0 + 2440587.5
86400 is the number of seconds in a day. 2440587.5 is the result of
julianday('1970-01-01') - the difference between Unix epoch and Julian
epoch.
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] millisecond precision for unixepoch values
I noticed today that JavaScript Date values (the number of milliseconds
since the Unix epoch) degrade to second precision when converted to
Julian date values and back using naive SQLite date/time function calls,
for example:
sqlite> SELECT strftime('%s', julianday(1219441430151/1000,
'unixepoch')) * 1000;
121944143
I suppose this is because "Unix has no tradition of directly
representing non-integer Unix time numbers as binary fractions."
Is there another way to preserve this information besides taking the
cumbersome additional step of constructing an intermediate ISO 8601 time
string from the JavaScript Date value in JavaScript and then converting
that to the Julian date (and back the same way)?
-myk
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
