sqlite3 - significant digits

[BuildWithData]

for some reason sqlite fails to round to 2 decimal digits sometimes, for example:

    select * from inflows_btc_bxfill
    
    +------------+-------------------+
    |  ref_date  |       TOTAL       |
    +------------+-------------------+
    | 2024-01-11 | 13796.2           |
    | 2024-01-12 | 4023.2            |
    | 2024-01-15 |                   |
    | 2024-01-16 | -1947.1           |
    | 2024-01-17 | 9256.8            |
    | 2024-01-18 | -4514.5           |
    | 2024-01-19 | -545.900000000002 |
    | 2024-01-22 | -2350.3           |

but it seems this is kinda random, for example inflows_btc_bfill is created with data from inflows_btc but:

  select total from inflows_btc_bfill where ref_date = '2024-03-22'

  +---------+
  |  TOTAL  |
  +---------+
  | -310.93 |
  +---------+

  select total from inflows_btc where ref_date = '2024-03-22'

  +-------------------+
  |       TOTAL       |
  +-------------------+
  | -310.929999999999 |
  +-------------------+

but what is weirder is that rounding is done in both cases:

BTC-ETF-Tracker/src/runner/db/inflows_btc.py

Line 41 in 450bf11

out["TOTAL"] = out.iloc[:, :-1].sum(axis=1).round(2)

BTC-ETF-Tracker/src/runner/db/inflows_btc_bfill.py

Line 38 in 450bf11

out = (extracted.iloc[:, 1:] - extracted.iloc[:, 1:].shift(1)).round(2)

[kaestro]

You mean you've stored datum in 2 decimal digits, but it sometimes fails to gives out the result as you've expected?

ex) you've stored 2024-01-19: -545.90 but it returned that value?

It might be caused from computer's float num storing system. so it's recommended you use rounding method to get the result you want.

[BuildWithData]

hey @kaestro, thanks for your feedback.

I agree but the thing is that numbers are rounded as you can see from the examples I have just added in the description.

What's your take ?
Wanna give it a try and fix this ?

[kaestro]

@BuildWithData

If I'm understood fine, you mean that you tried to store -310.93 into sqlite by sql query, but when trying to make a test it's giving out error for it's returning -310.929999... Am I right?

I think it's of concern on sqlite's settings if you want to fix it for you it would still store the decimals inaccurate form.

If you don't want to change the sqlite's settings, I think just rounding after you've got the output from sqlite would simply be okay

[BuildWithData]

@kaestro yeah you understood correctly

your second solution is not fixing the problem at the root and the thing is that people usually connect to the db and run queries, so I wanna see the expected number of digits in the table right there

we gotta go with the sqlite's settings then, can you help me with this ?

[kaestro]

@BuildWithData

Am I correct to understand that you're gonna give me an admin access to your sqlite server so that I can try to change the settings? Or Is there any way that I can change it as a code?

If I may, though I don't have enough experience with these kinda things, I would love to

[BuildWithData]

@kaestro you do not need any admin access, it's simpler than that. The idea of this whole project is to let anyone track BTC ETFs but in a don't trust verify way if we can say that.

All you gotta do is download and setup the repo on your local machine like shown here, grab the historical data here and then you are ready to start fixing this issue

any question/doubt, just write here :)

[kaestro]

@BuildWithData

Thank you for your support and giving me an offer. I'll start in 3 days

[BuildWithData]

it looks like we gotta define .sqliterc as they say here