candlestick_agg() functions

ToolkitTimescaleDB Toolkit functions are available under Timescale Community Edition. They are automatically included with Timescale, but must be installed separately for self-hosted TimescaleDB. Click to learn more.

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.

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:

More efficient because multiple accessors can reuse the same aggregate
Easier to reason about performance, because aggregation is separate from final computation
Easier to understand when calculations can be rolled up into larger intervals, especially in window functions and continuous aggregates
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

candlestick_agg: Aggregate tick data into an intermediate form for further calculation

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

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

rollup: Roll up multiple Candlestick aggregates

Function details

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

Name	Type	Description
`ts`	`TIMESTAMPTZ`	Timestamp associated with stock price
`price`	`DOUBLE PRECISION`	Stock quote/price at the given time
`volume`	`DOUBLE PRECISION`	Volume of the trade

Returns

Column	Type	Description
`agg`	`Candlestick`	An 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

Name	Type	Description
`ts`	`TIMESTAMPTZ`	Timestamp associated with stock price
`open`	`DOUBLE PRECISION`	Opening price of candlestick
`high`	`DOUBLE PRECISION`	High price of candlestick
`low`	`DOUBLE PRECISION`	Low price of candlestick
`close`	`DOUBLE PRECISION`	Closing price of candlestick
`volume`	`DOUBLE PRECISION`	Total volume of trades during the candlestick period

Returns

Column	Type	Description
`agg`	`Candlestick`	An 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

Name	Type	Description
`candlestick`	`Candlestick`	Candlestick aggregate

Returns

Column	Type	Description
`close`	`DOUBLE PRECISION`	The closing price

close_time(
    candlestick Candlestick
) RETURNS TIMESTAMPTZ

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

Required arguments

Name	Type	Description
`candlestick`	`Candlestick`	Candlestick aggregate

Returns

Column	Type	Description
`close_time`	`TIMESTAMPTZ`	The time at which the closing price occurred

high(
    candlestick Candlestick
) RETURNS DOUBLE PRECISION

Get the high price from a candlestick aggregate.

Required arguments

Name	Type	Description
`candlestick`	`Candlestick`	Candlestick aggregate

Returns

Column	Type	Description
`high`	`DOUBLE PRECISION`	The high price

high_time(
    candlestick Candlestick
) RETURNS TIMESTAMPTZ

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

Required arguments

Name	Type	Description
`candlestick`	`Candlestick`	Candlestick aggregate

Returns

Column	Type	Description
`high_time`	`TIMESTAMPTZ`	The first time at which the high price occurred

low(
    candlestick Candlestick
) RETURNS DOUBLE PRECISION

Get the low price from a candlestick aggregate.

Required arguments

Name	Type	Description
`candlestick`	`Candlestick`	Candlestick aggregate

Returns

Column	Type	Description
`low`	`DOUBLE PRECISION`	The low price

low_time(
    candlestick Candlestick
) RETURNS TIMESTAMPTZ

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

Required arguments

Name	Type	Description
`candlestick`	`Candlestick`	Candlestick aggregate

Returns

Column	Type	Description
`low_time`	`TIMESTAMPTZ`	The first time at which the low price occurred

open(
    candlestick Candlestick
) RETURNS DOUBLE PRECISION

Get the opening price from a candlestick aggregate.

Required arguments

Name	Type	Description
`candlestick`	`Candlestick`	Candlestick aggregate

Returns

Column	Type	Description
`open`	`DOUBLE PRECISION`	The opening price

open_time(
    candlestick Candlestick
) RETURNS TIMESTAMPTZ

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

Required arguments

Name	Type	Description
`candlestick`	`Candlestick`	Candlestick aggregate

Returns

Column	Type	Description
`open_time`	`TIMESTAMPTZ`	The time at which the opening price occurred

volume(
    candlestick Candlestick
) RETURNS DOUBLE PRECISION

Get the total volume from a candlestick aggregate.

Required arguments

Name	Type	Description
`candlestick`	`Candlestick`	Candlestick aggregate

Returns

Column	Type	Description
`volume`	`DOUBLE PRECISION`	Total 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

Name	Type	Description
`candlestick`	`Candlestick`	Candlestick aggregate

Returns

Column	Type	Description
`vwap`	`DOUBLE PRECISION`	The 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

Name	Type	Description
`candlestick`	`Candlestick`	The aggregate produced by a `candlestick` or `candlestick_agg` call

Returns

Column	Type	Description
`candlestick`	`Candlestick`	A new candlestick aggregate produced by combining the input candlestick aggregates

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,
    open(candlestick_agg("time", price, volume)),
    high(candlestick_agg("time", price, volume)),
    low(candlestick_agg("time", price, volume)),
    close(candlestick_agg("time", price, volume)),
    volume(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,
      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,
  open(candlestick),
  high(candlestick),
  low(candlestick),
  close(candlestick),
  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,
  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,
    open_time(candlestick),
    open(candlestick),
    high_time(candlestick),
    high(candlestick),
    low_time(candlestick),
    low(candlestick),
    close_time(candlestick),
    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,
    vwap(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,
    open(rollup(candlestick)),
    high(rollup(candlestick)),
    low(rollup(candlestick)),
    close(rollup(candlestick)),
    volume(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,
    open(candlestick),
    high(candlestick),
    low(candlestick),
    close(candlestick),
    volume(candlestick)
FROM (
    SELECT
        ts,
        symbol,
        candlestick(ts, open, high, low, close, volume)
    FROM historical_data
) AS _(ts, symbol, candlestick);
;

-- or

WITH cs AS (
    SELECT ts
      symbol,
      candlestick(ts, open, high, low, close, volume)
    FROM historical_data
)
SELECT 
    ts
    symbol,
    open(candlestick),
    high(candlestick),
    low(candlestick),
    close(candlestick),
    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,
    vwap(rollup(candlestick))
FROM (
    SELECT
        ts,
        symbol,
        candlestick(ts, open, high, low, close, volume)
    FROM historical_data
) AS _(ts, symbol, candlestick)
GROUP BY weekly_bucket, symbol
;

candlestick_agg() functions

Two-step aggregation

Functions in this group

Aggregate

Pseudo aggregate

Accessor

Rollup

Function details

candlestick_agg()

candlestick()

close()

close_time()

high()

high_time()

low()

low_time()

open()

open_time()

volume()

vwap()

rollup()

Extended examples

Get candlestick values from tick data

Create a continuous aggregate from tick data and roll it up

Starting from already-aggregated data

candlestick_agg() functions

Introduction

Two-step aggregation

Functions in this group

Aggregate

Pseudo aggregate

Accessor

Rollup

Function details

candlestick_agg()

candlestick()

close()

close_time()

high()

high_time()

low()

low_time()

open()

open_time()

volume()

vwap()

rollup()

Extended examples

Get candlestick values from tick data

Create a continuous aggregate from tick data and roll it up

Starting from already-aggregated data

Related Content