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
