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
