Round to 2dp with Python for SQLITE Insert

Question

I am trying to round my values to 2 decimal places in python (using jupyter notebook) and then insert those values into SQLITE3 but some of the values are not rounding:

Request:

count = cursor.execute("""SELECT test_user,
                          SUM(CASE WHEN start >= ? THEN round(julianday(end)*24, 1) - round(julianday(start)*24, 1) ELSE 0 END),
                          FROM test_table
                          WHERE start < ?
                          GROUP BY test_user""", (sdate.to_pydatetime(), edate.to_pydatetime())).fetchall()

Response:

[('1', 0, 8.0, 8.0),
 ('2', None, None, None),
 ('3', 0, 7.0, 7.0),
 ('5', 0.20000000298023224, 0.20000000298023224, 0.20000000298023224),
 ('6', 0.19999999552965164, 10.199999995529652, 10.199999995529652),
 ('7', 0.0, 0.0, 0.0),
 ('8', 5.100000001490116, 5.100000001490116, 5.100000001490116)]

As you can see some of the values have rounded but others have not. Is there another way to round the values?

Those numbers look right. For example, `0.2` cannot be represented exactly as an IEEE754 double, and `0.20000000298023224` is pretty close to the closest number to it that can be stored exactly. — Shawn, Dec 04 '19 at 12:59
Does this answer your question? [Is floating point math broken?](https://stackoverflow.com/questions/588004/is-floating-point-math-broken) — Shawn, Dec 04 '19 at 12:59

score 0 · Answer 1 · answered Dec 04 '19 at 10:28

I would suggest rounding the entire call to SUM, rather than the individual components making up the sum:

SELECT
    test_user,
    ROUND(SUM(CASE WHEN start >= ?
                   THEN 24*(JULIANDAY(end) - JULIANDAY(start))
                   ELSE 0 END), 2) AS user_sum
FROM test_table
WHERE
    start < ?
GROUP BY
    test_user;

As a side note, floating point arithmetic is not exact, so in your Python code you might still see non zero figures beyond the second decimal place, but they are not really significant.

Round to 2dp with Python for SQLITE Insert

1 Answers1