Design database schema
When you design a database schema, you need to think about what kind of data it stores.
This tutorial is about analyzing intraday stock data, so you need to create a schema that can handle candlestick data. This is what a typical candlestick looks like:
At least four data points are needed to create a candlestick chart: high, open, close, low.
You also need to have fields for the ticker symbol, time, and trading volume. The data fields we are using are:
|time||starting time of the minute|
|price_open||opening price of the stock|
|price_close||closing price of the stock|
|price_low||lowest price in the minute|
|price_high||highest price in the minute|
|trading_volume||trading volume in the minute|
Based on this, you can create a table called
CREATE TABLE public.stocks_intraday ("time" timestamptz NOT NULL,symbol text NULL,price_open double precision NULL,price_close double precision NULL,price_low double precision NULL,price_high double precision NULL,trading_volume int NULL);
This creates a regular PostgreSQL table with all the columns needed to ingest candlestick data records.
To use TimescaleDB features, you need to enable TimescaleDB, and create a hypertable from the
Enable TimescaleDB extension:
CREATE EXTENSION IF NOT EXISTS timescaledb;
Create hypertable from
/*stocks_intraday: name of the tabletime: name of the timestamp column*/SELECT create_hypertable('stocks_intraday', 'time');
At this point, you have an empty hypertable, ready to ingest time-series data.
Found an issue on this page?Report an issue!