Over the years I worked on many quantitative projects. Every project has different ways of storing market data. If you want to store your market data properly TimeScale DB might be a very good solution for you. It’s a Time Series database on to of PostgreSQL. It seems to be pretty quick and robust. It can allow you to do really interesting queries like resampling your data on the fly.
TimeScale is a plugin over PostgreSQL so you can use the usual SQL syntax to create a table:
/* Creating a table */
CREATE TABLE candles_1m (
time TIMESTAMPTZ NOT NULL,
open DOUBLE PRECISION NOT NULL,
high DOUBLE PRECISION NOT NULL,
low DOUBLE PRECISION NOT NULL,
close DOUBLE PRECISION NOT NULL,
volume DOUBLE PRECISION NOT NULL
);
This table is for 1m candles. It has a time column that represents the open of every bar and will be a column Timescale will use for indexing.
Now let’s add some random values to the table:
/* Adding Values */
INSERT INTO candles_1m(time, open, high, low, close, volume)
VALUES ('2021-03-17 14:00:00', 10, 12, 5.3, 11, 255);
INSERT INTO candles_1m(time, open, high, low, close, volume)
VALUES ('2021-03-17 14:01:00', 11, 15, 7, 9, 200);
INSERT INTO candles_1m(time, open, high, low, close, volume)
VALUES ('2021-03-17 14:02:00', 9, 11, 10, 10, 123);
INSERT INTO candles_1m(time, open, high, low, close, volume)
VALUES ('2021-03-17 14:03:00', 10, 17, 9, 14, 90);
INSERT INTO candles_1m(time, open, high, low, close, volume)
VALUES ('2021-03-17 14:04:00', 14, 39, 30, 20, 987);
INSERT INTO candles_1m(time, open, high, low, close, volume)
VALUES ('2021-03-17 14:05:00', 20, 26, 25, 25, 33);
INSERT INTO candles_1m(time, open, high, low, close, volume)
VALUES ('2021-03-17 14:06:00', 25, 39, 19, 30, 983);
Now we have a table with some values. You can check that to works:
select * from candles_1m;
For now, it’s a usual PostgreSQL table, to make it work with TimeScale you have to call the create_hypertable function:
SELECT create_hypertable('candles_1m', 'time', migrate_data => 'TRUE');
Here I pass to this function name of the table I created, a column in it for time index, and the flag to migrate existing data.
Now you can run the Timescale DB function and queries. So here for example the code that will create 5m bars from your 1-minute bars:
SELECT time_bucket('5 minutes', time) AS new_time,
first(open, time) as open,
MAX(high) as high,
MIN(low) as low,
last(close, time) as close,
sum(volume) as volume
FROM candles_1m
GROUP BY new_time
What is the editor tool used in this example with timescale db?