Query the data

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.

Creating a continuous aggregate

Connect to the Timescale database tsdb that contains the Twelve Data stocks dataset.

At the psql prompt, create the continuous aggregate to aggregate data every minute:


CREATE MATERIALIZED VIEW one_hour_candle
WITH (timescaledb.continuous) AS
    SELECT
        time_bucket('1 hour', time) AS bucket,
        symbol,
        FIRST(price, time) AS "open",
        MAX(price) AS high,
        MIN(price) AS low,
        LAST(price, time) AS "close",
        LAST(day_volume, time) AS day_volume
    FROM stocks_real_time
    GROUP BY bucket, symbol;

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:


SELECT add_continuous_aggregate_policy('one_hour_candle',
    start_offset => INTERVAL '3 hours',
    end_offset => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour');

Query the continuous aggregate

When you have your continuous aggregate set up, you can query it to get the OHLCV values.

Querying the continuous aggregate

Connect to the Timescale database that contains the Twelve Data stocks dataset.

At the psql prompt, use this query to select all AAPL OHLCV data for the past 5 hours, by time bucket:


SELECT * FROM one_hour_candle
WHERE symbol = 'AAPL' AND bucket >= NOW() - INTERVAL '5 hours'
ORDER BY bucket;

The result of the query looks like this:


bucket         | symbol  |  open   |  high   |   low   |  close  | day_volume
------------------------+---------+---------+---------+---------+---------+------------
 2023-05-30 08:00:00+00 | AAPL   | 176.31 | 176.31 |    176 | 176.01 |
 2023-05-30 08:01:00+00 | AAPL   | 176.27 | 176.27 | 176.02 |  176.2 |
 2023-05-30 08:06:00+00 | AAPL   | 176.03 | 176.04 | 175.95 |    176 |
 2023-05-30 08:07:00+00 | AAPL   | 175.95 |    176 | 175.82 | 175.91 |
 2023-05-30 08:08:00+00 | AAPL   | 175.92 | 176.02 |  175.8 | 176.02 |
 2023-05-30 08:09:00+00 | AAPL   | 176.02 | 176.02 |  175.9 | 175.98 |
 2023-05-30 08:10:00+00 | AAPL   | 175.98 | 175.98 | 175.94 | 175.94 |
 2023-05-30 08:11:00+00 | AAPL   | 175.94 | 175.94 | 175.91 | 175.91 |
 2023-05-30 08:12:00+00 | AAPL   |  175.9 | 175.94 |  175.9 | 175.94 |

Graph OHLCV data

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.

Graphing OHLCV data

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 Dashboards menu, click New Dashboard. In the New Dashboard page, click Add a new panel.
In the Visualizations menu in the top right corner, select Candlestick from the list. Ensure you have set the Twelve Data stocks dataset as your data source.
Click Edit SQL and paste in the query you used to get the OHLCV values.
In the Format as section, select Table.
Adjust elements of the table as required, and click Apply to save your graph to the dashboard.