Live Timescale Crypto Charts

In this tutorial we will use Timescale Cloud to create a live chart of crypto market data.
Timescale is a time-series SQL database providing fast analytics and scalability based on PostgreSQL.

Creating a new Timescale Database
Populating Data
Adding a Timescale Connection
Adding our First Table
- Adding a Time-Series Chart
Linking Tables and Charts

Creating new Timescale Database

Timescale can either be ran locally or you can use Timescale Cloud. To keep this tutorial simple we opted for cloud. Simply start a small service in your chosen region. The connection details should be provided, similar to shown:

Populating the Tables

To populate our tables, we have created a java program that uses org.knowm.xchange to connect to a number of exchanges and forward data to Timescale using the standard Postgres JDBC driver. To run it yourself, download the jar below or see the source in our github repo.

Download pgfeed.jar

To create the tables and run the feed, run it with the parameters:

# Linux download
curl https://www.timestored.com/pulse/tutorial/files/pgfeed.jar --output pgfeed.jar
chmod u+x pgfeed.jar

# Run File
java -jar pgfeed.jar jdbcURL username password

You should see output similar to below. The periods "." are the feed waiting. The larger letters T/o are it sending Trades and Orders respectively:

$ java -jar pgfeed.jar jdbc:postgresql://odrlt9p4il.j26c6ws540.tsdb.cloud.timescale.com:31319/tsdb?sslmode=require tsdbadmin wfs87wik6faoyrs9
Apr 12, 2023 11:48:57 AM com.timestored.qdb.PGBitcoinDemo exec
INFO: DROP TABLE IF EXISTS trade;
CREATE TABLE IF NOT EXISTS trade(etime TIMESTAMPTZ NOT NULL, sym TEXT,ex TEXT,account TEXT,type TEXT,price DOUBLE PRECISION,amount DOUBLE PRECISION,id BIGINT);
Apr 12, 2023 11:48:57 AM com.timestored.qdb.PGBitcoinDemo exec
INFO: DROP TABLE IF EXISTS orders;
CREATE TABLE IF NOT EXISTS orders(etime TIMESTAMPTZ NOT NULL, sym TEXT, ex TEXT, bid DOUBLE PRECISION, bsize DOUBLE PRECISION, ask DOUBLE PRECISION, asize DOUBLE PRECISION,
                bid1 DOUBLE PRECISION, bid2 DOUBLE PRECISION, bid3 DOUBLE PRECISION, bid4 DOUBLE PRECISION, bid5 DOUBLE PRECISION, bid6 DOUBLE PRECISION, bid7 DOUBLE PRECISION, bid8 DOUBLE PRECISION,
                bsize1 DOUBLE PRECISION, bsize2 DOUBLE PRECISION, bsize3 DOUBLE PRECISION, bsize4 DOUBLE PRECISION, bsize5 DOUBLE PRECISION, bsize6 DOUBLE PRECISION, bsize7 DOUBLE PRECISION, bsize8 DOUBLE PRECISION,
                ask1 DOUBLE PRECISION, ask2 DOUBLE PRECISION, ask3 DOUBLE PRECISION, ask4 DOUBLE PRECISION, ask5 DOUBLE PRECISION, ask6 DOUBLE PRECISION, ask7 DOUBLE PRECISION, ask8 DOUBLE PRECISION,
                asize1 DOUBLE PRECISION, asize2 DOUBLE PRECISION, asize3 DOUBLE PRECISION, asize4 DOUBLE PRECISION, asize5 DOUBLE PRECISION, asize6 DOUBLE PRECISION, asize7 DOUBLE PRECISION, asize8 DOUBLE PRECISION);
Apr 12, 2023 11:48:58 AM com.timestored.qdb.PGBitcoinDemo exec
INFO: SELECT create_hypertable('trade', 'etime', 'sym', 5);
Apr 12, 2023 11:48:58 AM com.timestored.qdb.PGBitcoinDemo exec
INFO: SELECT create_hypertable('orders', 'etime', 'sym', 5);
SLF4J: No SLF4J providers were found.
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See https://www.slf4j.org/codes.html#noProviders for further details.
,bitfinex-BTC,.o-SENT
,bitfinex-ETH,.o,bitfinex-XRP,.o,bitfinex-ADA,.o,bitfinex-DOG,.o,bitfinex-ETH,.o,bitfinex-ETC,.o.-SENT
-SENT
,bitfinex-XMR,.o............,bitfinex-LIN,.o,bitfinex-LTC,.o,bitfinex-TRX,.o.,bitfinex-XLM,.o-SENT
....,bitfinex-NEO,.o..............,bitfinex-XDC,.o...........-SENT
..............o,bitfinex-VET,.o.o.................-SENT
,bitstamp-BTC,.o,bitfinex-IOT,.o,bitstamp-ETH,.o-SENT
....,bitfinex-BTC,.,bitstamp-XRP,.o,bitstamp-ADA,.o..........,bitstamp-DOG,.o,bitstamp-ETH,.o.-SENT
.,bitstamp-LIN,.o,bitstamp-BCH,.o..........-SENT
.,bitstamp-LTC,.o.....................o..................................-SENT
.,bitstamp-XLM,.o...-SENT
..............,bitstamp-EUR,.oT..
.............,bitstamp-GBP,.o..

Add Timescale PostgreSQL Connection

Download Pulse

Download Pulse. Any platform is fine, then launch pulse.exe or pulse-log.sh. This will open the pulse UI in a web browser.
Click Connections to go to the connections screen.

Timescale is PostgreSQL (not forked/reimplemented), therefore the standard postgres driver can be used.

To add a connection in Pulse goto Connections->"Add Data Connection" and fill in your details.
You can try clicking test to see if the connection works before adding it.

type: Postgres
host: odrlt9p4il.j26c6ws540.tsdb.cloud.timescale.com
port: 31319
database: tsdb
username: tsdbadmin
password: wfs87wik6faoyrs9

Note: A very small number of users have received "org.postgresql.util.PSQLException: SSL error: Received fatal alert: handshake_failure" errors when attempting to add a timescale connection. This is due to the SSL certificates available in the java VM they are attempting to use. If this happens, try a different java version or contact us for help

Adding our First Table

On the dashboards listing page, click add
Click on the newly created dashboard to go into it. Either click on edit or toggle design mode once in the dashboard.
On the component bar, click "Table" to add a grid.
Within the Editor
- Select the Timescale server
- Enter the query:
```
SELECT * FROM TRADE ORDER BY etime DESC LIMIT 200;
```

Adding a Chart

The next SQL chart we want is a time-series. We use UNION to join the quotes + my trades + others trades. So that the bid/ask quotes are shown as lines, while the trades have triangle/circle shapes relative to the size of the trade:

SELECT etime,bid,ask,
  CAST(NULL as DOUBLE PRECISION) as mytrade_SD_CIRCLE,CAST(NULL as DOUBLE PRECISION) as mytrade_SD_SIZE,
  CAST(NULL as DOUBLE PRECISION) as trade_SD_TRIANGLE, CAST(NULL as DOUBLE PRECISION) as trade_SD_SIZE
  FROM orders WHERE sym = 'BTC/USD' AND etime > date_trunc('minute',now() - interval '1 minute')
UNION
select etime,
  CAST(NULL as DOUBLE PRECISION) as bid, CAST(NULL as DOUBLE PRECISION) as ask,
  price as mytrade_SD_CIRCLE,4+51*log(1+amount) as mytrade_SD_SIZE,
  CAST(NULL as DOUBLE PRECISION) as trade_SD_TRIANGLE, CAST(NULL as DOUBLE PRECISION) as trade_SD_SIZE
  from trade WHERE sym = 'BTC/USD' AND etime > date_trunc('minute',now() - interval '1 minute') AND account like 'ts%'
UNION 
select etime,
  CAST(NULL as DOUBLE PRECISION) as bid, CAST(NULL as DOUBLE PRECISION) as ask,
  CAST(NULL as DOUBLE PRECISION) as mytrade_SD_CIRCLE,CAST(NULL as DOUBLE PRECISION) as mytrade_SD_SIZE,
  price as trade_SD_TRIANGLE, 4+51*log(1+amount) as trade_SD_SIZE
  from trade WHERE sym = 'BTC/USD' AND etime > date_trunc('minute',now() - interval '1 minute') AND account NOT like 'ts%'
 ORDER BY etime ASC;

Linking Tables and Charts

Table Click Events allow users to click a row in a table, and have a chart show related data:

When a user clicks a row, the first few column names become populated with the value in that row.
i.e. ((sym))->"BTC/USD" The sym variable maps to the value "BTC/USD".
In the timeseries chart configuration
We can then use ((sym)) in the SQL query and the chart will be updated when the user clicks a row.

Well Done

Well done, we've created our Timescale Crypto Dashboard.

I found Timescale good to work with. Since it's mostly PostgreSQL based I was able to reuse a lot of my existing knowledge.
We added it to our top list of time-series databases.

To me the exciting features of Timescale are:

Hypertables - automatically partitions data by time, and optionally by space. Each hypertable is made up of child tables called chunks. Each chunk is assigned a range of time, and only contains data from that range. If the hypertable is also partitioned by space, each chunk is also assigned a subset of the space values.
Continuous Aggregates - First, Timescale processes the aggregation calculations when the aggregate is created and then stores the aggregation results to minimize re-calculation when new raw data is added.

Thanks for watching our demo. Please download Pulse to give it a try for yourself.