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:
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
The sqlite3 shell uses eight fractional digits; this happens to be more
than enough to ensure that the numbers cannot be rounded the wrong way.
(In any case, floats are stored as 64-bit binary.)
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.
Regards,
Clemens
