Introduction

Perform analysis of financial asset data. These specialized hyperfunctions make it easier to write financial analysis queries that involve candlestick data.

They help you answer questions such as:

  • What are the opening and closing prices of these stocks?
  • When did the highest price occur for this stock?

This function group uses the two-step aggregation pattern. In addition to the usual aggregate function, candlestick_agg, it also includes the pseudo-aggregate function candlestick. candlestick_agg produces a candlestick aggregate from raw tick data, which can then be used with the accessor and rollup functions in this group. candlestick takes pre-aggregated data and transforms it into the same format that candlestick_agg produces. This allows you to use the accessors and rollups with existing candlestick data.

Related hyperfunction groups

This group of functions uses the two-step aggregation pattern.

Rather than calculating the final result in one step, you first create an intermediate aggregate by using the aggregate function.

Then, use any of the accessors on the intermediate aggregate to calculate a final result. You can also roll up multiple intermediate aggregates with the rollup functions.

The two-step aggregation pattern has several advantages:

  1. More efficient because multiple accessors can reuse the same aggregate
  2. Easier to reason about performance, because aggregation is separate from final computation
  3. Easier to understand when calculations can be rolled up into larger intervals, especially in window functions and continuous aggregates
  4. Can perform retrospective analysis even when underlying data is dropped, because the intermediate aggregate stores extra information not available in the final result

To learn more, see the blog post on two-step aggregates.

warning

This function group includes some experimental functions. Experimental functions might change or be removed in future releases. We do not recommend using them in production. Experimental functions are marked with an Experimental tag.

Aggregate

candlestick_agg
ExperimentalAggregate tick data into an intermediate form for further calculation

Pseudo aggregate

candlestick
ExperimentalTransform pre-aggregated candlestick data into the correct form to use with candlestick_agg functions

Accessor

close
ExperimentalGet the closing price from a candlestick aggregate
close_time
ExperimentalGet the timestamp corresponding to the closing time from a candlestick aggregate
high
ExperimentalGet the high price from a candlestick aggregate
high_time
ExperimentalGet the timestamp corresponding to the high time from a candlestick aggregate
low
ExperimentalGet the low price from a candlestick aggregate
low_time
ExperimentalGet the timestamp corresponding to the low time from a candlestick aggregate
open
ExperimentalGet the opening price from a candlestick aggregate
open_time
ExperimentalGet the timestamp corresponding to the open time from a candlestick aggregate
volume
ExperimentalGet the total volume from a candlestick aggregate
vwap
ExperimentalGet the Volume Weighted Average Price from a candlestick aggregate

Rollup

rollup
ExperimentalRoll up multiple Candlestick aggregates
candlestick_agg(
ts TIMESTAMPTZ,
price DOUBLE PRECISION,
volume DOUBLE PRECISION
) RETURNS Candlestick

This is the first step for performing financial calculations on raw tick data. Use candlestick_agg to create an intermediate aggregate from your tick data. This intermediate form can then be used by one or more accessors in this group to compute final results.

Optionally, multiple such intermediate aggregate objects can be combined using rollup() before an accessor is applied.

If you're starting with pre-aggregated candlestick data rather than raw tick data, use the companion candlestick() function instead. This function transforms the existing aggregated data into the correct form for use with the candlestick accessors.

Required arguments
NameTypeDescription
tsTIMESTAMPTZTimestamp associated with stock price
priceDOUBLE PRECISIONStock quote/price at the given time
volumeDOUBLE PRECISIONVolume of the trade
Returns
ColumnTypeDescription
aggCandlestickAn object storing (timestamp, value) pairs for each of the opening, high, low, and closing prices, in addition to information used to calculate the total volume and Volume Weighted Average Price.
candlestick(
ts TIMESTAMPTZ,
open DOUBLE PRECISION,
high DOUBLE PRECISION,
low DOUBLE PRECISION,
close DOUBLE PRECISION,
volume DOUBLE PRECISION
) RETURNS Candlestick

This function transforms pre-aggregated candlestick data into a candlestick aggregate object. This object contains the data in the correct form to use with the accessors and rollups in this function group.

If you're starting with raw tick data rather than candlestick data, use candlestick_agg() instead.

Required arguments
NameTypeDescription
tsTIMESTAMPTZTimestamp associated with stock price
openDOUBLE PRECISIONOpening price of candlestick
highDOUBLE PRECISIONHigh price of candlestick
lowDOUBLE PRECISIONLow price of candlestick
closeDOUBLE PRECISIONClosing price of candlestick
volumeDOUBLE PRECISIONTotal volume of trades during the candlestick period
Returns
ColumnTypeDescription
aggCandlestickAn object storing (timestamp, value) pairs for each of the opening, high, low, and closing prices, in addition to information used to calculate the total volume and Volume Weighted Average Price.
close(
candlestick Candlestick
) RETURNS DOUBLE PRECISION

Get the closing price from a candlestick aggregate.

Required arguments
NameTypeDescription
candlestickCandlestickCandlestick aggregate
Returns
ColumnTypeDescription
closeDOUBLE PRECISIONThe closing price
close_time(
candlestick Candlestick
) RETURNS TIMESTAMPTZ

Get the timestamp corresponding to the closing time from a candlestick aggregate.

Required arguments
NameTypeDescription
candlestickCandlestickCandlestick aggregate
Returns
ColumnTypeDescription
close_timeTIMESTAMPTZThe time at which the closing price occurred
high(
candlestick Candlestick
) RETURNS DOUBLE PRECISION

Get the high price from a candlestick aggregate.

Required arguments
NameTypeDescription
candlestickCandlestickCandlestick aggregate
Returns
ColumnTypeDescription
highDOUBLE PRECISIONThe high price
high_time(
candlestick Candlestick
) RETURNS TIMESTAMPTZ

Get the timestamp corresponding to the high time from a candlestick aggregate.

Required arguments
NameTypeDescription
candlestickCandlestickCandlestick aggregate
Returns
ColumnTypeDescription
high_timeTIMESTAMPTZThe first time at which the high price occurred
low(
candlestick Candlestick
) RETURNS DOUBLE PRECISION

Get the low price from a candlestick aggregate.

Required arguments
NameTypeDescription
candlestickCandlestickCandlestick aggregate
Returns
ColumnTypeDescription
lowDOUBLE PRECISIONThe low price
low_time(
candlestick Candlestick
) RETURNS TIMESTAMPTZ

Get the timestamp corresponding to the low time from a candlestick aggregate.

Required arguments
NameTypeDescription
candlestickCandlestickCandlestick aggregate
Returns
ColumnTypeDescription
low_timeTIMESTAMPTZThe first time at which the low price occurred
open(
candlestick Candlestick
) RETURNS DOUBLE PRECISION

Get the opening price from a candlestick aggregate.

Required arguments
NameTypeDescription
candlestickCandlestickCandlestick aggregate
Returns
ColumnTypeDescription
openDOUBLE PRECISIONThe opening price
open_time(
candlestick Candlestick
) RETURNS TIMESTAMPTZ

Get the timestamp corresponding to the open time from a candlestick aggregate.

Required arguments
NameTypeDescription
candlestickCandlestickCandlestick aggregate
Returns
ColumnTypeDescription
open_timeTIMESTAMPTZThe time at which the opening price occurred
volume(
candlestick Candlestick
) RETURNS DOUBLE PRECISION

Get the total volume from a candlestick aggregate.

Required arguments
NameTypeDescription
candlestickCandlestickCandlestick aggregate
Returns
ColumnTypeDescription
volumeDOUBLE PRECISIONTotal volume of trades within the period
vwap(
candlestick Candlestick
) RETURNS DOUBLE PRECISION

Get the Volume Weighted Average Price from a candlestick aggregate.

For Candlesticks constructed from data that is already aggregated, the Volume Weighted Average Price is calculated using the typical price for each period (where the typical price refers to the arithmetic mean of the high, low, and closing prices).

Required arguments
NameTypeDescription
candlestickCandlestickCandlestick aggregate
Returns
ColumnTypeDescription
vwapDOUBLE PRECISIONThe volume weighted average price
rollup(
candlestick Candlestick
) RETURNS Candlestick

Combine multiple intermediate candlestick aggregates, produced by candlestick_agg or candlestick, into a single intermediate candlestick aggregate. For example, you can use rollup to combine candlestick aggregates from 15-minute buckets into daily buckets.

Required arguments
NameTypeDescription
ohlcCandlestickThe aggregate produced by a candlestick or candlestick_agg call
Returns
ColumnTypeDescription
ohlcCandlestickA new candlestick aggregate produced by combining the input candlestick aggregates

Query your tick data table for the opening, high, low, and closing prices, and the trading volume, for each 1 hour period in the last day:

SELECT
time_bucket('1 hour'::interval, "time") AS ts,
symbol,
toolkit_experimental.open(toolkit_experimental.candlestick_agg("time", price, volume)),
toolkit_experimental.high(toolkit_experimental.candlestick_agg("time", price, volume)),
toolkit_experimental.low(toolkit_experimental.candlestick_agg("time", price, volume)),
toolkit_experimental.close(toolkit_experimental.candlestick_agg("time", price, volume)),
toolkit_experimental.volume(toolkit_experimental.candlestick_agg("time", price, volume))
FROM stocks_real_time
WHERE "time" > now() - '1 day'::interval
GROUP BY ts, symbol
;
-- or
WITH cs AS (
SELECT time_bucket('1 hour'::interval, "time") AS hourly_bucket,
symbol,
toolkit_experimental.candlestick_agg("time", price, volume) AS candlestick
FROM stocks_real_time
WHERE "time" > now() - '1 day'::interval
GROUP BY hourly_bucket, symbol
)
SELECT hourly_bucket,
symbol,
toolkit_experimental.open(candlestick),
toolkit_experimental.high(candlestick),
toolkit_experimental.low(candlestick),
toolkit_experimental.close(candlestick),
toolkit_experimental.volume(candlestick)
FROM cs
;

Create a continuous aggregate on your stock trade data:

CREATE MATERIALIZED VIEW candlestick
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 minute'::interval, "time") AS ts,
symbol,
toolkit_experimental.candlestick_agg("time", price, volume) AS candlestick
FROM stocks_real_time
GROUP BY ts, symbol
;

Query your by-minute continuous aggregate over stock trade data for the opening, high, low, and closing (OHLC) prices, along with their timestamps, in the last hour:

SELECT ts,
symbol,
toolkit_experimental.open_time(candlestick),
toolkit_experimental.open(candlestick),
toolkit_experimental.high_time(candlestick),
toolkit_experimental.high(candlestick),
toolkit_experimental.low_time(candlestick),
toolkit_experimental.low(candlestick),
toolkit_experimental.close_time(candlestick),
toolkit_experimental.close(candlestick)
FROM candlestick
WHERE ts > now() - '1 hour'::interval
;

Roll up your by-minute continuous aggregate into daily buckets and return the Volume Weighted Average Price for AAPL for the last month:

SELECT
time_bucket('1 day'::interval, ts) AS daily_bucket,
symbol,
toolkit_experimental.vwap(toolkit_experimental.rollup(candlestick))
FROM candlestick
WHERE symbol = 'AAPL'
AND ts > now() - '1 month'::interval
GROUP BY daily_bucket
ORDER BY daily_bucket
;

Roll up your by-minute continuous aggregate into hourly buckets and return the the opening, high, low, and closing prices and the volume for each 1 hour period in the last day:

SELECT
time_bucket('1 hour'::interval, ts) AS hourly_bucket,
symbol,
toolkit_experimental.open(toolkit_experimental.rollup(candlestick)),
toolkit_experimental.high(toolkit_experimental.rollup(candlestick)),
toolkit_experimental.low(toolkit_experimental.rollup(candlestick)),
toolkit_experimental.close(toolkit_experimental.rollup(candlestick)),
toolkit_experimental.volume(toolkit_experimental.rollup(candlestick))
FROM candlestick
WHERE ts > now() - '1 day'::interval
GROUP BY hourly_bucket
;

If you have a table of pre-aggregated stock data, it might look similar this this format:

ts │ symbol │ open │ high │ low │ close │ volume
────────────────────────┼────────┼────────┼────────┼────────┼────────┼──────────
2022-11-17 00:00:00-05 │ VTI │ 195.67197.9195.45197.493704700
2022-11-16 00:00:00-05 │ VTI │ 199.45199.72198.03198.322905000
2022-11-15 00:00:00-05 │ VTI │ 201.5202.14198.34200.364606200
2022-11-14 00:00:00-05 │ VTI │ 199.26200.92198.21198.354248200
2022-11-11 00:00:00-05 │ VTI │ 198.58200.7197.82200.164538500
2022-11-10 00:00:00-05 │ VTI │ 194.35198.31193.65198.143981600
2022-11-09 00:00:00-05 │ VTI │ 190.46191.04187.21187.5313959600
2022-11-08 00:00:00-05 │ VTI │ 191.25193.31189.42191.664847500
2022-11-07 00:00:00-05 │ VTI │ 189.59190.97188.47190.663420000
2022-11-04 00:00:00-04 │ VTI │ 189.32190.3185.75188.943584600
2022-11-03 00:00:00-04 │ VTI │ 186.5188.09185.13186.543935600
2022-11-02 00:00:00-04 │ VTI │ 193.07195.27188.29188.344686000
2022-11-01 00:00:00-04 │ VTI │ 196196.44192.76193.439873800
2022-10-31 00:00:00-04 │ VTI │ 193.99195.17193.51194.035053900
2022-10-28 00:00:00-04 │ VTI │ 190.84195.53190.74195.293178800
2022-10-27 00:00:00-04 │ VTI │ 192.46193.47190.61190.853556300
2022-10-26 00:00:00-04 │ VTI │ 191.26194.64191.26191.754091100
2022-10-25 00:00:00-04 │ VTI │ 189.57193.16189.53192.943287100
2022-10-24 00:00:00-04 │ VTI │ 188.38190.12186.69189.514527800
2022-10-21 00:00:00-04 │ VTI │ 182.99187.78182.29187.493381200
2022-10-20 00:00:00-04 │ VTI │ 184.54186.99182.81183.272636200
2022-10-19 00:00:00-04 │ VTI │ 185.25186.64183.34184.872589100
2022-10-18 00:00:00-04 │ VTI │ 188.14188.7184.71186.463906800

You can use the candlestick function to transform the data into a form that you'll be able pass to all of the accessors and rollup functions. To show that your data is preserved, this example shows how these accessors return a table that looks just like your data:

SELECT
ts,
symbol,
toolkit_experimental.open(candlestick),
toolkit_experimental.high(candlestick),
toolkit_experimental.low(candlestick),
toolkit_experimental.close(candlestick),
toolkit_experimental.volume(candlestick)
FROM (
SELECT
ts,
symbol,
toolkit_experimental.candlestick(ts, open, high, low, close, volume)
FROM historical_data
) AS _(ts, symbol, candlestick);
;
-- or
WITH cs AS (
SELECT ts
symbol,
toolkit_experimental.candlestick(ts, open, high, low, close, volume)
FROM historical_data
)
SELECT
ts
symbol,
toolkit_experimental.open(candlestick),
toolkit_experimental.high(candlestick),
toolkit_experimental.low(candlestick),
toolkit_experimental.close(candlestick),
toolkit_experimental.volume(candlestick)
FROM cs
;

The advantage of transforming your data into the candlestick aggergate form is that you can then use other functions in this group, such as rollup and vwap.

Roll up your by-day historical data into weekly buckets and return the Volume Weighted Average Price:

SELECT
time_bucket('1 week'::interval, ts) AS weekly_bucket,
symbol,
toolkit_experimental.vwap(toolkit_experimental.rollup(candlestick))
FROM (
SELECT
ts,
symbol,
toolkit_experimental.candlestick(ts, open, high, low, close, volume)
FROM historical_data
) AS _(ts, symbol, candlestick)
GROUP BY weekly_bucket, symbol
;

Found an issue on this page?

Report an issue!

Keywords

Related Content