I don't understand how this is possible but for some reason, the bid/ask columnsonly show one decimal (should show 2):
arb_finder=# select * from order_book where exchange = 'Coinbase' and currency_1 = 'BTC' and timestamp >= timestamp '2024-07-23 10:36:36' and timestamp < timestamp '2024-07-23 10:36:40';
id | timestamp | currency_1 | currency_2 | bid_q | bid | ask | ask_q | exchange
---------+----------------------------+------------+------------+-----------+---------+---------+------------+----------
1407052 | 2024-07-23 10:36:36.038745 | BTC | USD | 0.102793 | 66957.8 | 66957.8 | 0.00105211 | Coinbase
1407053 | 2024-07-23 10:36:36.0886 | BTC | USD | 0.08678 | 66957.8 | 66960.2 | 0.00105212 | Coinbase
1407054 | 2024-07-23 10:36:36.138261 | BTC | USD | 0.08678 | 66957.8 | 66957.8 | 0.0342719 | Coinbase
1407055 | 2024-07-23 10:36:36.23883 | BTC | USD | 0.187353 | 66957.8 | 66957.8 | 0.0342719 | Coinbase
1407056 | 2024-07-23 10:36:36.288232 | BTC | USD | 0.406555 | 66957.8 | 66957.8 | 0.0343101 | Coinbase
1407057 | 2024-07-23 10:36:36.343896 | BTC | USD | 0.380639 | 66957.8 | 66957.8 | 0.0343101 | Coinbase
1407058 | 2024-07-23 10:36:36.535402 | BTC | USD | 0.380639 | 66957.8 | 66957.8 | 0.0343118 | Coinbase
1407059 | 2024-07-23 10:36:36.740284 | BTC | USD | 0.161437 | 66957.8 | 66957.8 | 0.0353636 | Coinbase
1407060 | 2024-07-23 10:36:36.972845 | BTC | USD | 0.162024 | 66957.8 | 66957.8 | 0.0353636 | Coinbase
1407061 | 2024-07-23 10:36:37.035819 | BTC | USD | 0.162024 | 66957.8 | 66957.8 | 0.0343118 | Coinbase
1407062 | 2024-07-23 10:36:37.334264 | BTC | USD | 0.370875 | 66957.8 | 66957.8 | 0.0240962 | Coinbase
1407063 | 2024-07-23 10:36:37.432583 | BTC | USD | 0.162024 | 66957.8 | 66957.8 | 0.0240962 | Coinbase
1407064 | 2024-07-23 10:36:37.492267 | BTC | USD | 0.370595 | 66957.8 | 66957.8 | 0.0240962 | Coinbase
1407065 | 2024-07-23 10:36:37.539213 | BTC | USD | 0.370595 | 66957.8 | 66957.8 | 0.101096 | Coinbase
1407066 | 2024-07-23 10:36:37.592276 | BTC | USD | 0.162024 | 66957.8 | 66957.8 | 0.101096 | Coinbase
1407067 | 2024-07-23 10:36:37.688685 | BTC | USD | 0.0766505 | 66957.8 | 66957.8 | 0.101096 | Coinbase
1407068 | 2024-07-23 10:36:37.790094 | BTC | USD | 0.0752607 | 66957.8 | 66957.8 | 0.101096 | Coinbase
1407069 | 2024-07-23 10:36:37.989782 | BTC | USD | 0.115134 | 66957.8 | 66957.8 | 0.101096 | Coinbase
1407070 | 2024-07-23 10:36:38.189248 | BTC | USD | 0.32433 | 66957.8 | 66957.8 | 0.101096 | Coinbase
1407071 | 2024-07-23 10:36:38.223394 | BTC | USD | 0.115134 | 66957.8 | 66957.8 | 0.101096 | Coinbase
1407072 | 2024-07-23 10:36:38.43737 | BTC | USD | 0.116524 | 66957.8 | 66957.8 | 0.101096 | Coinbase
1407073 | 2024-07-23 10:36:38.488503 | BTC | USD | 0.356598 | 66957.8 | 66957.8 | 0.0990962 | Coinbase
1407074 | 2024-07-23 10:36:38.527654 | BTC | USD | 0.336855 | 66957.8 | 66957.8 | 0.0990962 | Coinbase
1407075 | 2024-07-23 10:36:38.677178 | BTC | USD | 0.335465 | 66957.8 | 66957.8 | 0.0990962 | Coinbase
1407076 | 2024-07-23 10:36:38.885509 | BTC | USD | 0.336855 | 66957.8 | 66957.8 | 0.0990962 | Coinbase
1407077 | 2024-07-23 10:36:39.091913 | BTC | USD | 0.127504 | 66957.8 | 66957.8 | 0.0990962 | Coinbase
1407078 | 2024-07-23 10:36:39.238957 | BTC | USD | 0.148094 | 66957.8 | 66957.8 | 0.0990962 | Coinbase
1407079 | 2024-07-23 10:36:39.29049 | BTC | USD | 0.409813 | 66957.8 | 66957.8 | 0.0990962 | Coinbase
1407080 | 2024-07-23 10:36:39.334706 | BTC | USD | 0.201914 | 66957.8 | 66957.8 | 0.0990962 | Coinbase
1407081 | 2024-07-23 10:36:39.386161 | BTC | USD | 0.412944 | 66957.8 | 66957.8 | 0.0990962 | Coinbase
1407082 | 2024-07-23 10:36:39.4421 | BTC | USD | 0.412944 | 66957.8 | 66957.8 | 0.0990962 | Coinbase
1407083 | 2024-07-23 10:36:39.48631 | BTC | USD | 0.25149 | 66957.8 | 66957.8 | 0.0990962 | Coinbase
1407084 | 2024-07-23 10:36:39.541874 | BTC | USD | 0.2501 | 66957.8 | 66957.8 | 0.0990962 | Coinbase
1407085 | 2024-07-23 10:36:39.635424 | BTC | USD | 0.25149 | 66957.8 | 66957.8 | 0.0990962 | Coinbase
1407086 | 2024-07-23 10:36:39.690689 | BTC | USD | 0.2501 | 66957.8 | 66957.8 | 0.0990962 | Coinbase
1407087 | 2024-07-23 10:36:39.838419 | BTC | USD | 0.0404599 | 66957.8 | 66957.8 | 0.0990962 | Coinbase
1407088 | 2024-07-23 10:36:39.927487 | BTC | USD | 0.0404599 | 66957.8 | 66957.8 | 0.100148 | Coinbase
(37 rows)
I think the data stored is actually with a greater precision as I can see 2 decimals in the data pulled from Grafana:
(Grafana displays in BST, while the raw data is in UTC, hence the 1h difference)
What's even more confusing is that when importing the data in pandas, it also only shows with 1 decimal:
# Define your SQL query
query = """
SELECT
timestamp,
bid,
ask
FROM
order_book
WHERE
exchange = 'Coinbase' AND
currency_1 = 'BTC' AND
timestamp >= timestamp '2024-07-23 10:36:36' AND
timestamp < timestamp '2024-07-23 10:36:40'
ORDER BY timestamp
"""
df = pd.read_sql_query(query, engine)
print(df)
timestamp bid ask
0 2024-07-23 10:36:36.038745 66957.8 66957.8
1 2024-07-23 10:36:36.088600 66957.8 66960.2
2 2024-07-23 10:36:36.138261 66957.8 66957.8
3 2024-07-23 10:36:36.238830 66957.8 66957.8
4 2024-07-23 10:36:36.288232 66957.8 66957.8
5 2024-07-23 10:36:36.343896 66957.8 66957.8
6 2024-07-23 10:36:36.535402 66957.8 66957.8
7 2024-07-23 10:36:36.740284 66957.8 66957.8
8 2024-07-23 10:36:36.972845 66957.8 66957.8
9 2024-07-23 10:36:37.035819 66957.8 66957.8
10 2024-07-23 10:36:37.334264 66957.8 66957.8
11 2024-07-23 10:36:37.432583 66957.8 66957.8
12 2024-07-23 10:36:37.492267 66957.8 66957.8
13 2024-07-23 10:36:37.539213 66957.8 66957.8
14 2024-07-23 10:36:37.592276 66957.8 66957.8
15 2024-07-23 10:36:37.688685 66957.8 66957.8
16 2024-07-23 10:36:37.790094 66957.8 66957.8
17 2024-07-23 10:36:37.989782 66957.8 66957.8
18 2024-07-23 10:36:38.189248 66957.8 66957.8
19 2024-07-23 10:36:38.223394 66957.8 66957.8
20 2024-07-23 10:36:38.437370 66957.8 66957.8
21 2024-07-23 10:36:38.488503 66957.8 66957.8
22 2024-07-23 10:36:38.527654 66957.8 66957.8
[...]
What am I missing here?
Here's the structure of the order_book
table:
arb_finder=# \d order_book
Table "public.order_book"
Column | Type | Collation | Nullable | Default
------------+-----------------------------+-----------+----------+----------------------------------------
id | bigint | | not null | nextval('order_book_id_seq'::regclass)
timestamp | timestamp without time zone | | | CURRENT_TIMESTAMP
currency_1 | text | | not null |
currency_2 | text | | not null |
bid_q | real | | not null |
bid | real | | not null |
ask | real | | not null |
ask_q | real | | not null |
exchange | text | | not null |
Indexes:
"order_book_pkey" PRIMARY KEY, btree (id)
Edit: I've just tried to insert a test entry with 2 decimals to rule out an issue with the actual data inserted and it still displays with only one decimal:
arb_finder=# insert into order_book (timestamp, currency_1, currency_2, bid_q, bid, ask, ask_q, exchange) values (now(), 'TEST', 'USD', 0.102793, 66957.82, 66957.83, 0.00105211, 'Coinbase');
arb_finder=# select * from order_book where currency_1 = 'TEST';
id | timestamp | currency_1 | currency_2 | bid_q | bid | ask | ask_q | exchange
---------+----------------------------+------------+------------+----------+---------+---------+------------+----------
1462509 | 2024-07-23 11:57:19.658942 | TEST | USD | 0.102793 | 66957.8 | 66957.8 | 0.00105211 | Coinbase
(1 row)
I then tried with a smaller number and it worked:
arb_finder=# insert into order_book (timestamp, currency_1, currency_2, bid_q, bid, ask, ask_q, exchange) values (now(), 'TEST', 'USD', 0.102793, 57.82, 66957.83, 0.00105211, 'Coinbase');
INSERT 0 1
arb_finder=# select * from order_book where currency_1 = 'TEST'; id | timestamp | currency_1 | currency_2 | bid_q | bid | ask | ask_q | exchange
---------+----------------------------+------------+------------+----------+---------+---------+------------+----------
1462509 | 2024-07-23 11:57:19.658942 | TEST | USD | 0.102793 | 66957.8 | 66957.8 | 0.00105211 | Coinbase
1464557 | 2024-07-23 11:58:11.950465 | TEST | USD | 0.102793 | 57.82 | 66957.8 | 0.00105211 | Coinbase
(2 rows)
Is it that I should be using a different data type (eg double precision instead of real)? But if that's the case, how come Grafana seems to be pulling 2 decimal precision?
Bid
and Ask
are of data type real
. The PostgreSQL version is 11.22 (Raspbian 11.22-0+deb10u2). And SHOW extra_float_digits;
returns 0. But Bid_q
and Ask_q
are also real
and show 6 decimals.
The answer is in the lacking precision of floating point values. When you convert numbers between the decimal system and the binary representation used for floating point numbers, the result is usually not exact. For example, the binary representation of 0.1 has infinitely many decimal places. PostgreSQL tries to show only as many digits as can be guaranteed to be loss-free during this conversion. One of the reasons for that is that the displayed numbers should stay the same if you pg_dump
and restore a database.
For that reason, PostgreSQL has a parameter extra_float_digits
that controls how many digits are shown. The default value of 0 guarantees the stability of numbers as described above. Note that the implementation and meaning of this parameter changed in v12, the default value is now 1, but the general idea is the same.
It seems like the Grafana interface is using a higher value for extra_float_digits
than the default setting you are using in psql
.
Note that the number of digits that are shown is not the same for each number; it depends on the value.
I am not certain why Grafana does what it does, but my guess is that it uses the default setting for extra_float_digits
from PostgreSQL v12 on. So perhaps all you have to do to make the difference disappear is to upgrade to a supported PostgreSQL version.