candlestick_agg() functions

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

• ohlc()

Two-step aggregation

Hide content

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.

Functions in this group

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

Function details

candlestick_agg()

ExperimentalExperimental TimescaleDB Toolkit functions are not suitable for production environments. They may have bugs and may cause data loss. Click to learn more.

Introduced in Toolkit v1.12.0

Hide content

`

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

Returns

candlestick()

ExperimentalExperimental TimescaleDB Toolkit functions are not suitable for production environments. They may have bugs and may cause data loss. Click to learn more.

Introduced in Toolkit v1.12.0

Hide content

`

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

Returns

close()

ExperimentalExperimental TimescaleDB Toolkit functions are not suitable for production environments. They may have bugs and may cause data loss. Click to learn more.

Introduced in Toolkit v1.12.0

Hide content

`

close(

`

    candlestick Candlestick

`

) RETURNS DOUBLE PRECISION

`

Get the closing price from a candlestick aggregate.

Required arguments

Returns

close_time()

ExperimentalExperimental TimescaleDB Toolkit functions are not suitable for production environments. They may have bugs and may cause data loss. Click to learn more.

Introduced in Toolkit v1.12.0

Hide content

`

close_time(

`

    candlestick Candlestick

`

) RETURNS TIMESTAMPTZ

`

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

Required arguments

Returns

high()

ExperimentalExperimental TimescaleDB Toolkit functions are not suitable for production environments. They may have bugs and may cause data loss. Click to learn more.

Introduced in Toolkit v1.12.0

Hide content

`

high(

`

    candlestick Candlestick

`

) RETURNS DOUBLE PRECISION

`

Get the high price from a candlestick aggregate.

Required arguments

Returns

high_time()

ExperimentalExperimental TimescaleDB Toolkit functions are not suitable for production environments. They may have bugs and may cause data loss. Click to learn more.

Introduced in Toolkit v1.12.0

Hide content

`

high_time(

`

    candlestick Candlestick

`

) RETURNS TIMESTAMPTZ

`

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

Required arguments

Returns

low()

ExperimentalExperimental TimescaleDB Toolkit functions are not suitable for production environments. They may have bugs and may cause data loss. Click to learn more.

Introduced in Toolkit v1.12.0

Hide content

`

low(

`

    candlestick Candlestick

`

) RETURNS DOUBLE PRECISION

`

Get the low price from a candlestick aggregate.

Required arguments

Returns

low_time()

ExperimentalExperimental TimescaleDB Toolkit functions are not suitable for production environments. They may have bugs and may cause data loss. Click to learn more.

Introduced in Toolkit v1.12.0

Hide content

`

low_time(

`

    candlestick Candlestick

`

) RETURNS TIMESTAMPTZ

`

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

Required arguments

Returns

open()

ExperimentalExperimental TimescaleDB Toolkit functions are not suitable for production environments. They may have bugs and may cause data loss. Click to learn more.

Introduced in Toolkit v1.12.0

Hide content

`

open(

`

    candlestick Candlestick

`

) RETURNS DOUBLE PRECISION

`

Get the opening price from a candlestick aggregate.

Required arguments

Returns

open_time()

ExperimentalExperimental TimescaleDB Toolkit functions are not suitable for production environments. They may have bugs and may cause data loss. Click to learn more.

Introduced in Toolkit v1.12.0

Hide content

`

open_time(

`

    candlestick Candlestick

`

) RETURNS TIMESTAMPTZ

`

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

Required arguments

Returns

volume()

ExperimentalExperimental TimescaleDB Toolkit functions are not suitable for production environments. They may have bugs and may cause data loss. Click to learn more.

Introduced in Toolkit v1.12.0

Hide content

`

volume(

`

    candlestick Candlestick

`

) RETURNS DOUBLE PRECISION

`

Get the total volume from a candlestick aggregate.

Required arguments

Returns

vwap()

ExperimentalExperimental TimescaleDB Toolkit functions are not suitable for production environments. They may have bugs and may cause data loss. Click to learn more.

Introduced in Toolkit v1.12.0

Hide content

`

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

Returns

rollup()

ExperimentalExperimental TimescaleDB Toolkit functions are not suitable for production environments. They may have bugs and may cause data loss. Click to learn more.

Introduced in Toolkit v1.12.0

Hide content

`

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

Returns

Extended examples

Get candlestick values from tick data

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 from tick data and roll it up

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
;

Starting from already-aggregated data

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.67 │  197.9 │ 195.45 │ 197.49 │  3704700
 2022-11-16 00:00:00-05 │ VTI    │ 199.45 │ 199.72 │ 198.03 │ 198.32 │  2905000
 2022-11-15 00:00:00-05 │ VTI    │  201.5 │ 202.14 │ 198.34 │ 200.36 │  4606200
 2022-11-14 00:00:00-05 │ VTI    │ 199.26 │ 200.92 │ 198.21 │ 198.35 │  4248200
 2022-11-11 00:00:00-05 │ VTI    │ 198.58 │  200.7 │ 197.82 │ 200.16 │  4538500
 2022-11-10 00:00:00-05 │ VTI    │ 194.35 │ 198.31 │ 193.65 │ 198.14 │  3981600
 2022-11-09 00:00:00-05 │ VTI    │ 190.46 │ 191.04 │ 187.21 │ 187.53 │ 13959600
 2022-11-08 00:00:00-05 │ VTI    │ 191.25 │ 193.31 │ 189.42 │ 191.66 │  4847500
 2022-11-07 00:00:00-05 │ VTI    │ 189.59 │ 190.97 │ 188.47 │ 190.66 │  3420000
 2022-11-04 00:00:00-04 │ VTI    │ 189.32 │  190.3 │ 185.75 │ 188.94 │  3584600
 2022-11-03 00:00:00-04 │ VTI    │  186.5 │ 188.09 │ 185.13 │ 186.54 │  3935600
 2022-11-02 00:00:00-04 │ VTI    │ 193.07 │ 195.27 │ 188.29 │ 188.34 │  4686000
 2022-11-01 00:00:00-04 │ VTI    │    196 │ 196.44 │ 192.76 │ 193.43 │  9873800
 2022-10-31 00:00:00-04 │ VTI    │ 193.99 │ 195.17 │ 193.51 │ 194.03 │  5053900
 2022-10-28 00:00:00-04 │ VTI    │ 190.84 │ 195.53 │ 190.74 │ 195.29 │  3178800
 2022-10-27 00:00:00-04 │ VTI    │ 192.46 │ 193.47 │ 190.61 │ 190.85 │  3556300
 2022-10-26 00:00:00-04 │ VTI    │ 191.26 │ 194.64 │ 191.26 │ 191.75 │  4091100
 2022-10-25 00:00:00-04 │ VTI    │ 189.57 │ 193.16 │ 189.53 │ 192.94 │  3287100
 2022-10-24 00:00:00-04 │ VTI    │ 188.38 │ 190.12 │ 186.69 │ 189.51 │  4527800
 2022-10-21 00:00:00-04 │ VTI    │ 182.99 │ 187.78 │ 182.29 │ 187.49 │  3381200
 2022-10-20 00:00:00-04 │ VTI    │ 184.54 │ 186.99 │ 182.81 │ 183.27 │  2636200
 2022-10-19 00:00:00-04 │ VTI    │ 185.25 │ 186.64 │ 183.34 │ 184.87 │  2589100
 2022-10-18 00:00:00-04 │ VTI    │ 188.14 │  188.7 │ 184.71 │ 186.46 │  3906800

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
;