To look at OHLCV values, the most effective way is to create a continuous aggregate. You can create a continuous aggregate to aggregate data for each hour, then set the aggregate to refresh every hour, and aggregate the last two hours' worth of data.
Connect to the Timescale database
tsdbthat contains the Twelve Data stocks dataset.
At the psql prompt, create the continuous aggregate to aggregate data every minute:
When you create the continuous aggregate, it refreshes by default.
Set a refresh policy to update the continuous aggregate every hour, if there is new data available in the hypertable for the last two hours:
When you have your continuous aggregate set up, you can query it to get the OHLCV values.
Connect to the Timescale database that contains the Twelve Data stocks dataset.
At the psql prompt, use this query to select all
AAPLOHLCV data for the past 5 hours, by time bucket:
The result of the query looks like this:
When you have extracted the raw OHLCV data, you can use it to graph the result in a candlestick chart, using Grafana. To do this, you need to have Grafana set up to connect to your TimescaleDB database.
Ensure you have Grafana installed, and you are using the TimescaleDB database that contains the Twelve Data stocks dataset set up as a data source.
In Grafana, from the
New Dashboard. In the
New Dashboardpage, click
Add a new panel.
Visualizationsmenu in the top right corner, select
Candlestickfrom the list. Ensure you have set the Twelve Data stocks dataset as your data source.
Edit SQLand paste in the query you used to get the OHLCV values.
Format assection, select
Adjust elements of the table as required, and click
Applyto save your graph to the dashboard.
Found an issue on this page?Report an issue!