On Fri, Apr 22, 2016 at 8:43 AM, Clemens Ladisch <clemens at ladisch.de> wrote:
> Stephan Beal wrote:
> > On Thu, Apr 21, 2016 at 4:12 PM, jrhgame <jrhgame at 163.com> wrote:
> >> SELECT julianday('2016-04-15 12:10:10') ==>2457494.00706
> >> SELECT datetime(2457494.00706) ==>2016-04-15 12:10:09
> >
> > fwiw, i've done lots and lots of testing with round-trip conversions
> > between those two formats, and it cannot be done 100% reliably (at least
> on
> > consumer-grade hardware). There is always a minority percentage of cases
> > which round/truncate one second here or there.
>
> With enough precision, seconds can be handled just fine:
>
That's the operative term resp. limitation, yes.
> sqlite> select julianday('2000-01-01 00:00:00');
> ...> select julianday('2000-01-01 00:00:01');
> ...> select julianday('2000-01-01 00:00:02');
> 2451544.5
> 2451544.50001157
> 2451544.50002315
>
but i beg to differ that that works in 100% of cases. My test inputs read
Julian timestamps from Fossil source repos and convert them back and forth
between ISO8601 using published algorithms for doing so (as opposed to
using sqlite for doing so). In such cases, a small minority (roughly
0.5-3%, but that's heavily platform-dependent (32-bit ARM performs more
poorly here)) exhibit 1-second truncation/rounding errors.
> You can get problems only if
> - you are not using enough precision, or
> - the number does not represent a full second, but some random point
> somewhere in the middle between two whole seconds.
>
The latter is exactly the case here - fossil remembers the millisecond part
during commits, as sown on the "D" line here:
http://fossil-scm.org/index.html/artifact/4abf607937fac8e0
--
----- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
