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" timestamp 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 table time: 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!